count in filtered list (2000sr1)

Topic

New Reply

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?
Thanks in advance!

Reply | Quote

Replies

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

Steve

Reply | Quote

Thanks, Steve.
So if the existing formula, which counts everything whether it’s filtered or not, is
=COUNTIF(D$18:D$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.

Reply | Quote

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%5B/url%5D

Steve

Reply | Quote

=SUMPRODUCT(SUBTOTAL(3,OFFSET(D$18:D$2313,ROW(D$18:D$2313)-MIN(ROW(D$18:D$2313)),,1))*(D$18:D$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.

Reply | Quote

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.

Reply | Quote