Share via

Using Groupby function on multiple criterion.

Anonymous
2024-10-04T10:14:31+00:00
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. 

  1. ""              ""              ""          monthwise totals matching last 3 wild card characters in Transactions column

  2. ""              ""              ""          monthwise total Credits = online Credits+Cash Credits 

  3. ""               ""             ""          monthwise  total debits= online Debits+Cash Debits

  4. ""               ""             ""          all above items between start date to end date criteria

  5. ""              ""              ""          all above items totals monthwise,transaction wise(matching last 3 text letters), and      

                                               Category wise.
Microsoft 365 and Office | Excel | For home | Windows

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.

0 comments

Answer accepted by question author

Ashish Mathur 102K Reputation points Volunteer Moderator
2024-10-07T23:05:40+00:00

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.

Was this answer helpful?

1 person found this answer helpful.
0 comments

Answer accepted by question author

Ashish Mathur 102K Reputation points Volunteer Moderator
2024-10-07T08:43:57+00:00

You are welcome. I do not work with Table references. Do it youself.

Was this answer helpful?

1 person found this answer helpful.
0 comments

Answer accepted by question author

Ashish Mathur 102K Reputation points Volunteer Moderator
2024-10-07T05:30:25+00:00

Try this

TRIM(TEXTAFTER(B1:B10,"-",-1))

Was this answer helpful?

1 person found this answer helpful.
0 comments

Answer accepted by question author

Ashish Mathur 102K Reputation points Volunteer Moderator
2024-10-05T23:34:02+00:00

Solution file can be downloaded from here.

Was this answer helpful?

1 person found this answer helpful.
0 comments

Answer accepted by question author

Ashish Mathur 102K Reputation points Volunteer Moderator
2024-10-05T23:33:45+00:00

Solution file can be downloaded from here.

Was this answer helpful?

1 person found this answer helpful.
0 comments

15 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2024-10-05T12:17:12+00:00

    Some:

    No.1

    =GROUPBY(IFERROR(DATE(YEAR(Table6[[#All],[Date]]),MONTH(Table6[[#All],[Date]]),1),"Date"),Table6[[#All],[online Credit]:[Cash Debit]],SUM,3,0)

    No.2

    =PIVOTBY(CHOOSE({1,2},DATE(YEAR(Table6[Date]),MONTH(Table6[Date]),1),RIGHT(Table6[Transactions],3)),,Table6[[online Credit]:[Cash Debit]],SUM,,0)

    Was this answer helpful?

    0 comments