0

I'm putting together a personal budget and have added a tab to track daily expenses. I've allocated a "code" against a type of expenditure, and then a "sub-code" for more specific information. So for e.g if I buy a bottle of wine for home, this would be classified as "G" (for groceries), "2" for alcohol. I then have a summary table to track expenses against each code and sub-code. In the pic you'll see an example of dummy expenditure. In the section circled in red I want to calculate all entries that have "G;1", "G;2" etc etc. Hope this is clear enough and thanks in advance. excel formula

Improve this question
3
  • 1
    Please don’t post images of code, error messages, or other textual data.
    – tripleee
    Jun 21, 2021 at 6:01
  • Have you looked at using a PivotTable to build the summary, which would then also let you drill into any figure to see what line items contribute to it, either G1 or G2 or "all of G" as you need (or any other code or sub-code)
    – AdamV
    Jun 21, 2021 at 12:07
  • If I correctly understand the question, you want to use a SUMIFS formula.
    – Isolated
    Jun 21, 2021 at 13:31

1 Answer 1

Reset to default
1

It could be done by formula like this:

=SUM(($E$11:$E$27=$B4)*($F$11:$F$27=C$3)*$D$11:$D$27)

Note: This works for Excel 365 and newer. In older Excel versions this formula must be inserted as array formula (with CTLR+SHIFT+ENTER)

See image:

enter image description here

I strongly suggest to use named table at least for table with expense entries. It manages ranges automatically for you.

=SUM((Table1[Code]=$B4)*(Table1[SubCode]=C$3)*Table1[Ammount])

Note: Stretching is not working here correctly for horizontal direction.
So use 
=SUM((Table1[Code]=$C4)*(Table1[SubCode]=D$3)*Table1[Ammount])
=SUM((Table1[Code]=$D4)*(Table1[SubCode]=E$3)*Table1[Ammount])
etc for each next column

enter image description here

Improve this answer
2
  • Thanks for this, really appreciate it. Not sure why but in the table example you sent, I've replicated everything on your image but still get the error about 1+1
    – EL1085
    Jun 22, 2021 at 2:04
  • @EL1085 You can try to compare it with example in Excel Online which uses same syntax as Excel 365.
    – Lluser
    Jun 22, 2021 at 5:14

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .