Share via

SUM by Category & Sub-Category

Anonymous
2022-12-15T21:26:15+00:00

Split from this thread.

Liz,

I hope I can piggy-back off your response because I face a similar challenge. I'm a first time user so, if this is not the proper protocol, please let me know.

In my spreadsheet, I have Date (mm/yy) in Column A, Amount in Column C, Major Expense Category in Column E and Expense Sub-Category in Column F. I want to sum Expenses by Month in Column A, Column E, and Column F.  In H2, I have coded:=UNIQUE(LEFT(A2:A322,2)&" "&E2:E322&" "&F2:F322) and I get my unique combinations of MM CAT Sub-CAT just fine.In I2, I coded"=SUMIF(C2:C322,LEFT(A2:A322,2)&" "&E2:E322&" "&F2:F322,H2#) and all my results are 0. I have tried all combinations of SUMIF and SUMIFS I can think of but can't get a match between Column I and Column H so that it will sum.

Thanks ..... GCI Chair - Dave

Microsoft 365 and Office | Excel | For business | 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

  1. Lz365 38,191 Reputation points Volunteer Moderator
    2022-12-17T08:26:11+00:00

    Hi Dave

    As you seem to be curious :) Be aware you can avoid manually adding the MONTH column to your data source table. This can be done behind the scene with Get & Transform aka Power Query

    This sample does it. All Power Query steps were done with the User Interface

    Hope this helps

    1 person found this answer helpful.
    0 comments

Answer accepted by question author

  1. Ashish Mathur 101.8K Reputation points Volunteer Moderator
    2022-12-15T23:12:34+00:00

    Hi,

    In cell I2, enter this formula

    =SUMIF(H2#,LEFT(A2:A141,2)&" "&E2:E141&" "&F2:F141,C2:C141)

    Hope this helps.

    1 person found this answer helpful.
    0 comments

Answer accepted by question author

  1. Ashish Mathur 101.8K Reputation points Volunteer Moderator
    2022-12-15T22:52:30+00:00

    Hi,

    The last input of the SUMIF() function is the sum range. H2# cannot be the sum range.

    1 person found this answer helpful.
    0 comments

7 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Lz365 38,191 Reputation points Volunteer Moderator
    2022-12-15T21:52:22+00:00

    Hi Dave

    (I split your question as it seems to be a slightly different) First of all THANKS for searching !!!

    It's late my time and I haven't read your question very carefully (Apologies). The solution seems (to me) straightforward with a Pivot Table. However, and to confirm, could you clarify the content of your column A?

    Many people believe that i.e. 'Jan 2022' is a Date but it's not because a cell displays as 'Jan 2022' that it's really a Date value. So, assuming your "Dates" start in A2, could you enter i.e. in G2: =ISNUMBER(A2) and tell me if the result of that formula is TRUE or FALSE?

    And to save time, if you have no restriction, could you upload and share i.e. with OneDrive (or any other file sharing service) a representative sample of your workbook?

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2022-12-15T23:01:21+00:00

    Lz,

    Thanks for your reply. Based on your time zone, you are in Europe. I am not in dire need so please answer at your convenience.

    One correction. This data is exported from a bank account and the "DATE" I referred to is actually a text field that represents month and day. I'm only interested in the month portion for my summarizations. Sorry for misstating.

    Here is a link to the file that contains a good sampling of the data: https://1drv.ms/x/s!ArI\_jcXqTwqMinfOForM76zTCO\_j?e=ogrfIG

    As an aside, I retired 4 years ago after a 45 year career in IT. The last version of Microsoft Office I used was 2007 and I never had much of a need to dabble in the intricacies of Functions so I'm "knocking off the rust" and learning at the same time. I will certainly look into Pivot Tables while awaiting your reply.

    Thank you again for replying at such a late hour (for you).

    Dave

    0 comments