Sumifs with Subtotal

[aghaffar82]

Hello Sirs

Hope you are doing great,

I am stuck with a unique requirement where I want to see totals of filtered cells based on multiple criteria.

I want to see total of for instant Group U10 for Paid, Pending+Empty Cells in the Cells D25 & D26.
Total of Paid should be total entries of Paid in column D multiplied by Membership Fees in the Column C. Shown in Cell D25
Total of Pending+Empty Cells should be total entries of Pending + Empty Cells in column D multiplied by Membership Fees in the Column C. Shown in Cell D26.

Hope it makes some sense, I am attaching a screenshot of my file as well.

 

[dnorm]

Hi aghaffar

These 2 formulas should help there.

Paid copy and place this formulae in D25- =SUMIFS(C:C,B:B,"U10",D:D,"Paid")
Pending copy and place this formulae in D26- =SUMIFS(C:C,B:B,"U10",D:D,"Pending")

SUMIFS works like this: =SUMIFS(Add what you would like to sum (Column C), Based on in data located here (Column B), Only where the data says ("U10"), And Based on in data located here (Column D), Only where the data says ("Paid"))

Change "D:D" to match column of the month you want for the rest
hope this helps

 

[aghaffar82]

Hello Sir,

Thank you so much for your quick response, but probably I couldn't explain, basically, I don't want to hard-code U10, my purpose is if I FILTER column B which contains groups including U10, the formulas should auto-adjust.

Also, in case of Pending, I want to count empty cells as pending as well. I mean, if a few cells have Paid and Pending values and some are empty, I want to consider empty cells as pending as well.

=SUMPRODUCT(SUBTOTAL(9,OFFSET($C$8,ROW($C$8:$C$25)-ROW($C$8),0)),(D8:D25="Paid")+0)

I am using the above formula which is perfectly working but how can I add it with TWO CONDITIONS i.e. Pending and empty cells considered as one. I want to achieve this result in cell D27.
Hope this makes better sense now.

Thank you again.

 

[dnorm]

Hi,

If you place your dropdown filter list in B25, you can still use the SUMIFS formula but change "U10" to B25, then when you require that info just select which group you want.
This way D25 and D26 will auto update to the item selected.

This might not be the route you were thinking, but it works and updates without issue.

I have never been great with SUMPRODUCT formulas sorry, I did try to use NOT and AND statements to get around the issue, but none of them worked for me.

 

[nygex]

Just multiply in the second criteria. Basically, because it's an array, anything that multiplies zero will cancel that row out. Here is a formula from a current sheet I have,

=SUMPRODUCT((Detail!$AM$18:$AM$847>=AK29)*(Detail!$AM$18:$AM$847<AL29)*(SUBTOTAL(103,OFFSET(Detail!$O$18,ROW(Detail!$O$18:$O$847)-MIN(ROW(Detail!$O$18:$O$847)),0))))

 

[nygex]

Just to clarify, TRUE= 1 and FALSE = 0. So if the criterai is TRUE * TRUE it's included. If TRUE * FALSE, it's not. Hope that makes sense.

 

[aghaffar82]

Thank you Sirs for giving it a try, however, I was able to find a working solution which allows me to just use the standard table filters (no validation drop-down) and see the results instantly. Here is what is working for me:

=SUMPRODUCT(SUBTOTAL(9,OFFSET($C$8,ROW($C$8:$C$25)-ROW($C$8),0)),(D8:D25="Paid")+0)
=SUMPRODUCT(SUBTOTAL(9,OFFSET($C$8,ROW($C$8:$C$25)-ROW($C$8),0)),((D8:D25="Pending"))+(D8:D25=""))

The first formula is giving me results where it meets the condition of "Paid".
The second instance is giving me values with an OR condition also, it shows whether there is "Pending" or an "Empty/Blank Cell". The plus sign after pending is being used for OR condition.

Trust it helps some other users also.

Best Regards and Thank you again