making a sumif within a filtered subtotal

[Chenboy2]

I'm recording income and expenses in a spreadsheet and I filter the not all of the transactions always appear. I have a subtotal that I want to display what the filtered income and expenses are. Right now I can only do a sumif for the total amounts, and not when the amounts are filtered. Is it possible to create a formula that will sumif the filtered income and expenses?

 

[Tom Urtis]

You can use the SUBTOTAL function with the 9 (SUM) argument for a filtered list. Here's an example, with other common numeric arguments.

 

[Chenboy2]

Thanks, I understand this but this is what I'm trying to do:

My spreadsheet has income and expenses, so both positive and negative numbers. I want to filter a certain group of transactions and I want the sun a sumif function of subtotal and not the entire transactions.

Example
Total transactions: 1000, 500, -200, -100, -50, 300, 385, 600, -750, -200
Filtered transactions: 500, -200, -100, 300, 385
I want the sumif to calcualte the income amoutn of 1185, and expense of -300

How do i create a function that I can do this for any filter I run?

 

[Tom Urtis]

To sum just positive numbers and just negative numbers separately at the same time, assuming your numbers are in column B, from B2:B100, try these two formulas for a filtered list...

Positive only:
=SUMPRODUCT((B2:B100>0)+0,SUBTOTAL(9,OFFSET(B2:B100,ROW(B2:B100)-ROW(B2),0,1)))

Negative only:
=SUMPRODUCT((B2:B100<0)+0,SUBTOTAL(9,OFFSET(B2:B100,ROW(B2:B100)-ROW(B2),0,1)))

 

[Chenboy2]

Thank you!