Subtotal function with an If criteria

[Kathy L.]

Hello,
If the following is example data:
A B C
1 Person $ Selected
2 Tom 10000 Y
3 Jack 15000 Y
4 Tom 20000 N
5 Tom 1000 Y
6 Jack 5000 N
7
8 51000

And cell B8 has the Subtotal(9,b2:b6) function, so I can sum based on a
filter on column A, is there a way to include a criteria in this subtotal
where Column C (selected) is Y?

 

[Per Jessen]

Hi

Use the sumif function:

=SUMIF(C2:C6,"Y",B2:B6)

Regards,
Per

 

[Kathy L.]

Thank you, however this won't subtotal only the filtered data, filtered in
Column A.
I'd like the sum to total based on the Filtered Column A, and only the Y's,
however I don't want the user to have to filter on Column C to select the Y's.

 

[Kathy L.]

Found the answer to my question on another thread from Domenic:

To count the number of cells that contain a specific text string:

SUMPRODUCT(SUBTOTAL(9,OFFSET(B2:B6,ROW(B2:B6)-MIN(ROW(B2:B6)),0,1)),(C2:C6="Y")+0)

This seems to do the trick.