CountIf Limitation?

[Deltaecho]

I have a column that contains three text descriptions ie.
Intermediate Average or Advance. Using the CountIf
function , I am able to count each description separately
and then show the overall total.
Can I modify the Countif to add up the three descriptions
and display a single total of their occurance?
Thanks very much

 

[JE McGimpsey]

One way:

=SUMPRODUCT(COUNTIF(A:A,{"Intermediate","Advance","Average"}))

 

[Alan Beban]

Or simply

=SUM(COUNTIF(A:A,{"Intermediate","Advance","Average"}))

Alan Beban

 

[Harlan Grove]

Or simply

=SUM(COUNTIF(A:A,{"Intermediate","Advance","Average"}))

This does work with array constants, but it doesn't with derived arrays.
It's probably better to use SUMPRODUCT all the time since it'd always work.

 

[Alan Beban]

This does work with array constants, but it doesn't with derived arrays.
It's probably better to use SUMPRODUCT all the time since it'd always work.

Probably so; although, though I'm not sure what derived arrays are, it
might be that SUM works with them if array entered.

Alan Beban

 

[Harlan Grove]

work.

Probably so; although, though I'm not sure what derived arrays are, it
might be that SUM works with them if array entered.

Derived arrays are arrays produced by nonconstant expressions. The term
'constant array' applies to syntactic tokens (lookup up that bit of jargon
yourself, it's widely used by real programmers) like {1,2;3,4;5,6}. I
haven't seen any term in online help for arrays produced by expressions like

(ROW(1:3)-1)*2+COLUMNA:B)

which generates the same result as the preceding constant array. I use
'derived array' because the result it an array and it's derived by
evaluating the expression. Would 'evaluated array' be clearer for you? What
term do you use to distinguish constant arrays from arrays that aren't
constant arrays?

Anyway, your SUM formula works entered normally when the 2nd argument to
COUNTIF is a constant array but returns #VALUE! if that argument were what
I've been calling a derived array. Your formula would work with either kind
of array if entered as part of an array formula.

SUMPRODUCT would always return the same result entered normally or as part
of an array formula with constant or derived array arguments. There may be
more to type with SUMPRODUCT, but there's less to remember. In my experience
most people prefer doing more and thinking less, or perhaps they just behave
consistently with such a preference.

 

[Alan Beban]

Thanks for the explication.

. . . I use
'derived array' because the result is an array and it's derived by
evaluating the expression. . . .Anyway, your SUM formula works entered normally
when the 2nd argument to COUNTIF is a constant array but returns #VALUE!
if that argument were what I've been calling a derived array.

So I take it that

=SUM(COUNTIF(A:A,E2:G2)) involves a derived array. I suppose worse than
returning a #VALUE! error message, it in fact returns an incorrect count
if entered normally.

Alan Beban

 

[Harlan Grove]

So I take it that

=SUM(COUNTIF(A:A,E2:G2)) involves a derived array. I suppose worse than
returning a #VALUE! error message, it in fact returns an incorrect count
if entered normally.

You're right - it doesn't return #VALUE!. It's one of the odder things Excel
does. If you enter random letters in part of column A, and A, I and U in
E2:G2, select E4:G4, type in the formula

=COUNTIF(A:A,$E2:$G2)

and press [Ctrl]+[Enter], you get the results of implicit array indexing, so
some cells may be nonzero. When you select E5:J6 and enter the formula

=SUM(COUNTIF(A:A,$E$2:$G$2))

in the same way, you get the same results in each column of E5:G6 and zeros
in H5:J6. Very, very weird. (I should have remembered it. Thanks for the
reminder.)

Has anyone ever used this? Is it documented anywhere in online help?