Search code examples
excel-2007

Combining between Sumif and subtotal with tables in Excel

I have the following table in Excel

A - B - C

amount - type - tag

4 - Debit - nice

5 - Credit - nice

32 - Debit - bad

31 - Credit - bad

for calculation of total I used the following formula:

=sumif([type],"Credit",[amount])-sumif([type],"Debit",[amount])

I got 0, which is right.

but then I filtered the table to show "nice" tags only, but the result didn't change to 1, it remained 0.

How can I solve this problem so that subtotal is calculated when values are filtered according to tag?

Solution

  • Finally, I found a formula that works! It doesn't work in Google sheets though...

    =SUMPRODUCT(SUBTOTAL(109,OFFSET(D$2:D$160,ROW(D$2:$D$160)-ROW(D$2),,1)),--(E$2:E$160="CREDIT"))-SUMPRODUCT(SUBTOTAL(109,OFFSET(D$2:D$160,ROW(D$2:$D$160)-ROW(D$2),,1)),--(E$2:E$160="DEBIT"))