Subtotal with Sumifs

[aghaffar82]

Hello Sirs,

Hope you are doing great

I have a simple worksheet where I want to see totals of filtered values. A formula which could give total values based on applied filter on column B (Group) in cells D25 & D26 based on criteria Paid and Pending+Empty Cell (empty cell is also considered as pending).

Would be greatful and would be available for anymore clarification if required.

Looking forward
Abdul Ghaffar

[onrainbow]

PivotTables make it easier to get results.
Nevertheless, if you do it as a formula, you can take a few steps.

First enter the following formula in the Q column and give it a heading of "Match".

q5
=MATCH([Group],[Group],0)

Create a "Match" column and enter the formula below.
However, only one "Group" filter should be selected for normal results

d25
=IFERROR(AVERAGEIFS(Table1[Membership Fee],Table1[Match],SUBTOTAL(101,Table1[Match]),Table1[Sep-19],"Paid"),"")

d26
=IFERROR(AVERAGEIFS(Table1[Membership Fee],Table1[Match],SUBTOTAL(101,Table1[Match]),Table1[Sep-19],"<>Paid"),"")

[aghaffar82]

Hello Sir,

Thank you so much for your detailed response,

I have been able to achieve accurate results for one section i.e. Paid using the below formula:
=SUMPRODUCT(SUBTOTAL(9,OFFSET($C$8,ROW($C$8:$C$25)-ROW($C$8),0)),(D8:D25="Paid")+0)

However, can you please help how can I get filtered totals using the above formula with two conditions i.e.
I want to use it where cell in range D8:D25 contains either "Pending" or an empty/blank cell.
both empty cell and cell containing Pending should be considered as one thing.
Hope it makes better sense now.

Thank you again. Best Regards

[onrainbow]

It's a nice formula.
I'm glad to know the formula I didn't know.
Thank you.

See below for non-Paid cases.

=SUMPRODUCT(SUBTOTAL(9,OFFSET($C$5,ROW($C$5:$C$22)-ROW($C$5),0))*(D$5:D$22<>"Paid"))

or

=SUMPRODUCT(SUBTOTAL(9,OFFSET($C$5,ROW($C$5:$C$22)-ROW($C$5),0))*(D$5:D$22={"Pending",""}))

[aghaffar82]

Thank you so much, it worked like a charm.
Best Regards