This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
| Date | Transactions | online Credit | Cash Credit | online Debit | Cash Debit | Category | |
|---|---|---|---|---|---|---|---|
| 02/06/2024 | sdrcv- BOB | 50 | 10 | BB | |||
| 21/06/2024 | juyg- BOI | 10 | 20 | CC | |||
| 30/06/2024 | hgtju-BOI | 100 | 10 | 30 | AA | ||
| 01/07/2024 | fres-NOL | 20 | 40 | BB | |||
| 06/07/2024 | hgty- BOB | 30 | 10 | 10 | AA | ||
| 10/07/2024 | juihy-HDB | 60 | 30 | BB | |||
| 15/07/2024 | kiu-BOB | 15 | 40 | CC | |||
| 10/08/2024 | gtry-BOI | 200 | 50 | 50 | AA | ||
| 20/08/2024 | mki-BOI | 60 | CC |
this is my Table1 data.
1.HOW to use GROUPBY function monthwise totals of entire table.
"" "" "" monthwise totals matching last 3 wild card characters in Transactions column
"" "" "" monthwise total Credits = online Credits+Cash Credits
"" "" "" monthwise total debits= online Debits+Cash Debits
"" "" "" all above items between start date to end date criteria
"" "" "" all above items totals monthwise,transaction wise(matching last 3 text letters), and
Category wise.
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.
Answer accepted by question author
In cell A19, enter this formula
=DROP(GROUPBY(HSTACK(TEXT(A1:A16,"yyyymm"),TEXT(A1:A16,"mmm-yyyy"),TRIM(TEXTAFTER(B1:B16,"-",-1))),C1:F16,SUM,3,0,,NOT(ISERROR(XLOOKUP(TRIM(TEXTAFTER(B2:B16,"-",-1,,,"")),I2:I5,I2:I5)))),,1)
Hope this helps.
Answer accepted by question author
You are welcome. I do not work with Table references. Do it youself.
Answer accepted by question author
Try this
TRIM(TEXTAFTER(B1:B10,"-",-1))
Answer accepted by question author
Solution file can be downloaded from here.
Answer accepted by question author
Solution file can be downloaded from here.
Thank You Mathur Ji.
Result 1: Monthwise Totals: on entire data array.
| Month | Online Credits | Cash Credits | Online Debits | Cash Debits |
|---|---|---|---|---|
| June-2024 | 150 | 20 | 40 | 20 |
| July-2024 | 80 | 45 | 40 | 90 |
| Aug-2024 | 200 | 50 | 50 | 60 |
Result 1.1: Monthwise Totals with single Column search criteria
Result 2.
| Month | Transactions > Last 3 letters match | Online Credits | Cash Credits | Online Debits | Cash Debits |
|---|---|---|---|---|---|
| June-2024 | BOB | 50 | 0 | 10 | 0 |
| June-2024 | BOI | 100 | 20 | 30 | 20 |
| Jul-2024 | NOL | 20 | 0 | 0 | 40 |
| Jul-2024 | BOB | 0 | 45 | 10 | 50 |
| Jul-2024 | HDB | 60 | 0 | 30 | 0 |
| Aug-2024 | BOI | 200 | 50 | 50 | 60 |
Result 3.& 4.
| Drop down list of months>> | Jun-2024 | 150 | 20 | 40 | 20 |
|---|
| Month | Credits | Debits |
|---|---|---|
| June-2024 | 170 | 60 |
| July-2024 | 125 | 130 |
| Aug-2024 | 250 | 110 |
Result 5.
| Between Dates | Start Date | 30/06/2024 | End Date | 01/08/2024 | |||
|---|---|---|---|---|---|---|---|
| Date | Transactios | Online Credit | Online Debit | Cash Credit | Cash debit | Category | |
| 30/06/2024 | hgtju-BOI | 100 | 10 | 30 | AA | ||
| 01/07/2024 | fres-NOL | 20 | 40 | BB | |||
| 06/07/2024 | hgty- BOB | 30 | 10 | 10 | AA | ||
| 10/07/2024 | juihy-HDB | 60 | 30 | BB | |||
| 15/07/2024 | kiu-BOB | 15 | 40 | CC |
Result 6.1
Category Criteria = AA
| Date | Transactios | Online Credit | Online Debit | Cash Credit | Cash debit | Category |
|---|---|---|---|---|---|---|
| 30/06/2024 | hgtju-BOA | 100 | 10 | 30 | AA | |
| 06/07/2024 | hgty- BOB | 30 | 10 | 10 | AA | |
| 10/08/2024 | gtry-BOI | 200 | 50 | 50 | AA |
Result 6.2
Category Criteria = AA
| Month/on entire data | Online Credit | Online Debit | Cash Credit | Cash debit | Category |
|---|---|---|---|---|---|
| June-2024 | 100 | 10 | 30 | 0 | AA |
| in Entire array | 300 | 90 | 90 | 10 | AA |
Please note Mathur ji, I want to learn Using Groupby/Pivotby function, which has so many parameters to use. How to get results using GROUPBY/PIVOTBY function only. Please also note that I have modified my base data table, Thank you once again.