Counting with multiple matching criteria
Thanks Biff
--
Gary''s Student - gsnu200858
"T. Valko" wrote:
Listed in order of efficiency:
=COUNTIF(A:A,B1)+COUNTIF(A:A,C1)+COUNTIF(A:A,D1)+C OUNTIF(A:A,E1)
=SUMPRODUCT(COUNTIF(A:A,B1:E1))
=SUMPRODUCT(--(ISNUMBER(MATCH(A1:A3500,B1:E1,0))))
=SUM(--(A1:A3500=TRANSPOSE(B1:B4)))
=SUMPRODUCT(--(A1:A3500=B1:E1))
--
Biff
Microsoft Excel MVP
"Gary''s Student" wrote in message
...
A colleague came to me yesterday and complained about the length of
formulas.
She needs to count occurrences of values in a table that meet any of
several
criteria. The table is pure text with no blanks. Her formula was
something
like:
=COUNTIF(G7:G3147,"open")+COUNTIF(G7:G3147,"pendin g
review")+COUNTIF(G7:G3147,"review complete")+COUNTIF(G7:G3147,"assigned")
I pointed out that she did not need repeated COUNTIF()'s and to use:
=SUM(COUNTIF(G7:G3147,{"open","pending review","review
complete","assigned"}))
She was satisfied, but returned this morning and wanted to know if the
criteria could be completely removed from the formula and stored in a
table.
I put the match values in Z1 thru Z4 and tried:
=SUM(COUNTIF(G7:G3147,Z1:Z4)) but this returns zero.
Any suggestions for putting the criteria in a little table and referring
to
that table??
--
Gary''s Student - gsnu200858
|