Thread: Make countif ignore hidden rows?

1. My spreadsheet has hundreds of rows, so I filter on the criterion I want in Col A. Then I want to count how many times each text value occurs in Col B, but not include that value in rows hidden by the filter. I can't see that =COUNTIF() does this, and =SUBTOTAL() seems not to work at all with text strings. Is there a function I can use to make this work? Many thanks, Frank

2. I am not sure there is a simple answer to this

I have a formula in the attached example.
It counts based upon the data in column A in the Range.
The count is based upon A matching the entry in D2

It uses Subtotal 3 to count, and sumproduct with row functions and offset,
to make sure the count works and is filtered.

=SUMPRODUCT((SUBTOTAL(3,OFFSET(\$A\$8:\$A\$108,ROW(\$A\$ 8:\$A\$108)-ROW(\$A\$8),0,1))),--(\$A\$8:\$A\$108=\$D2))

See attached for Sum example as well

[attachment=88307:ConditionalFilteredCount.xls]

3. Subtotal should work. If TEXT is the problem it seems that you may be using =Subtotal(2,...) for COUNT, when you want =Subtotal(3,...) for COUNTA. Count ignores Text, CountA does NOT.

Steve

4. Hello Steve, I tried with your suggestion but could not make it work.

In a column of many rows, the formula relating to col U would be =SUBTOTAL(3,U9:U500,"1.Yes") which produces an error (I also tried with SUBTOTAL(103,U9:U500,"1.Yes") to ignore hidden rows but also produced an error). Maybe subtotal uses text strings differently?

Using =COUNTIF(U\$9:U\$500,"2.No") I get the right result until I filter the list, it fails to ignore hidden rows.

Can we get subtotal to 'count' occurrences of a text string in a cell?

(this all relates to answers to questions in a quiz - one question per column, filter for members of one team, count how many gave each answer)

Thanks
Frank

5. Subtotal does not take criteria. Filter column U to only display "1.Yes" and then use.
=SUBTOTAL(3,U9:U500)

Otherwise you will have to use either an ARRAY type formula [Count(if(.....)) or a SUMPRODUCT(...) variation] or a COUNTIFS (if you have XL2007) to count with multiple criteria and include what you want to filter on in the criteria (though this will not be LIVE if you filter something more)

You must decide if you want to calculated based on ALL filtered data or base it on criteria. I think if you want both you will have to create your own function...
Steve

6. Thankyou Steve, I now can better see how subtotal works.

What I need is to filter on a different column, selecting in rows according to team membership, then count how many times my chosen value appears in the other column.

There will be several chosen values, but I can use a set of rows at the top of my sheet to count each one.

I'm going to try out the suggestion by AKW as well as some other ideas, and post a sample worksheet to demonstrate my problem.

Many thanks to all for contributing. Frank

7. If you are looking for some type of summary table, by various groupings, it may be that a pivot table could fit your needs...

Steve

8. Frank,

If I understand you correctly what you may be looking for are the database functions, DCOUNTA in particular.

I've attached a workbook to demonstrate what I think you want. Pay particular attention to how the TEXT criteria values are entered. I always seem to forget how this is done and have to refer back to the help files to refresh my memory. I hope this helps.

RG

9. Frank,
Did you try Andrew's suggestion? It will work with filtered data.

10. Thanks to all of you you contributed to this thread - I should have realised at once that I was dealing with a database / datalist, and that the DCountA function was what I needed!
Due to all your suggestions, I've got it to work using it. Many thanks again, Frank

11. Hi all,

What if the data is in the below format and i have hidden away (NOT FILTERED OUT) row E5 but still is being counted. I just want to count how many "Frank" are there? Can anyone help me urgently? I have included current formula inplace (F7) that is generating result "3" in cell E7. Thanks in advance.

A B C D E F
2 0.00 0.00 0.00 0.00 Raiden
3 0.00 0.00 0.00 0.00 Raiden
4 0.00 0.00 0.00 0.00 Frank
6 0.00 0.00 0.00 0.00 Frank
3 =COUNTIF(E2:E6,"Frank")

12. 0.00 0.00 0.00 0.00 Raiden
0.00 0.00 0.00 0.00 Raiden
0.00 0.00 0.00 0.00 Frank
0.00 0.00 0.00 0.00 Frank
3 COUNTIF(E11:E15,"Frank")

13. How about a formula like:
=SUMPRODUCT(SUBTOTAL(103,OFFSET(E2:E6,ROW(E2:E6)-ROW(E2),0,1)),--(E2:E6="Frank"))
For your 1st post

And for your second post:
=SUMPRODUCT(SUBTOTAL(103,OFFSET(E11:E15,ROW(E11:E1 5)-ROW(E11),0,1)),--(E11:E15="Frank"))

The subtotal works for both filtered and/or hidden rows

Steve

14. You might also try the COUNTIFS() function. This will allow you to count based on the filter condition in column A and the desired text value in B.

15. jackhh,
COUNTIFS will not ignore hidden rows, whether they are explicitly hidden or hidden by a filter. COUNTIFS (like COUNTIF) will only ignore based on the conditions of the formula.

Steve

Page 1 of 2 12 Last

Posting Permissions

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