Subtotal function with an If criteria


K

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?
 
Ad

Advertisements

K

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.
 
Ad

Advertisements

K

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top