Thread: making a sumif within a filtered subtotal Thanks: 0 Likes: 0

1. making a sumif within a filtered subtotal

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?  Reply With Quote

2. Re: making a sumif within a filtered subtotal

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

3. Re: making a sumif within a filtered subtotal

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?  Reply With Quote

4. Re: making a sumif within a filtered subtotal

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)))  Reply With Quote

5. Re: making a sumif within a filtered subtotal

Thank you!   Reply With Quote

User Tag List

Tags for this Thread

expenses, filter and total, filtered, sumif, sumif and subtotal  Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•