# Thread: count in filtered list (2000sr1)

1. ## count in filtered list (2000sr1)

How can I make some COUNTIF statements referring to column, say, A, count only the matching cells when the list is filtered by one or more criteria in other columns in the list?

2. ## Re: count in filtered list (2000sr1)

Use SUBTOTAL if you are only basing it on the filtering in place. Subtotal uses ONLY the visible data.

Steve

3. ## Re: count in filtered list (2000sr1)

Thanks, Steve.
So if the existing formula, which counts everything whether it's filtered or not, is
=COUNTIF(D\$18\$2313,"1")
(and subsequent cells look for the value "2" and so on), how do I combine this with the SUBTOTAL trick, preferably in a way which tolerates the filter option 'show all', ie no filter at all?
I figure I need a "SUBTOTAL IF" function, which only exists in my head!
It comes from a survey generating responses from 1 to 5, and I want to produce reports deriving from particular districts, age groups, and so on.

4. ## Re: count in filtered list (2000sr1)

This sounds like you might want to do a PIVOT TABLE of your data. This will summarize and sum/count/average, etc based on various combinations of your data.

There is NO SUBTOTALIF. You can use subtotal which can count, sum, average, etc the "visible" items in a filtered list (this is "live" when the filters change the subtotals will change) and also include the count, sum, average,etc functions to get ALL the data to compare them directly.

You can create multiconditional IFs (like countif) but they include average, var, min, max, etc using Array formulas:
See Chip Pearson's site for a "tutorial"
http://www.cpearson.com/excel/array.htm

Steve

5. ## Re: count in filtered list (2000sr1)

=SUMPRODUCT(SUBTOTAL(3,OFFSET(D\$18\$2313,ROW(D\$18\$2313)-MIN(ROW(D\$18\$2313)),,1))*(D\$18\$2313=1))

I assumed the criterion (1) to be a number, not a text-formatted number ("1"). You can even have this criterion in a cell of its own and reference that cell.

6. ## Re: count in filtered list (2000sr1)

Sorry late in thanking for these, chaps.
This job no longer top of the pile.
Will come back on this topic if need to.
Thanks again, very much.

#### Posting Permissions

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