# Thread: Use COUNTA in SUBTOTAL

1. ## Use COUNTA in SUBTOTAL

Hi,
I have a list of data that is evaluated by either a blank, "Y" or "N" in a column.
I use COUNTA to calculate the quantity of "Y" in the list.
I now need to filter the list according to another column called Category, but the COUNTA does not show only the filtered data.

A B

1 Category Y/N
2 Cat B N
3 Cat A N
4 Cat A Y
5 Cat C Y
6 Cat B
7
8 Total Yes: 2 (this amount is calculated through COUNTA, but column A will now be filtered)

Thanks
Pierre

2. Hi
you might need to take a look at the SUBTOTAL function

3. Hi
Yes I did, but could not get the SUBTOTAL to only add only the cells containing a "Y" like COUNTIF does.
SUBTOTAL adds all text cells, regardless of content.

Is there a way to combine a COUNTIF and a SUBTOTAL formula?
I tried something like:
=COUNTIF(SUBTOTAL(3,B2:B20),"Y")
but it does not work.
I also tried to begin the formula with SUBTOTAL by it also does not work.

Thanks

4. I also tried to begin the formula with SUBTOTAL by it also does not work.
show us the formula you used in this case.

5. To select only the cells marked "Yes", I used:
=COUNTIF(B6:B17,"Yes")
This returned a total of 5 because there are 5 cells marked with "Yes". The rest are marked No

But column A contains a criteria A, B or C
Criteria A only has one "Yes", meaning if I filter only criteria A then the answer should only be 1.

I cannot get SUBTOTAL to work that it only shows me the 1

I hope you understand.

#### Posting Permissions

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