Counting with multiple matching criteria
Thank you Shane - a very clever idea!
--
Gary''s Student - gsnu200858
"Shane Devenshire" wrote:
Hi,
Here's a short one:
=SUMPRODUCT(--(G1:G10=W1:Z1))
Adjust the ranges but note that the criteria W1:Z1 need to run horizontally
to keep the formula this short.
--
If this helps, please click the Yes button.
Cheers,
Shane Devenshire
"Gary''s Student" wrote:
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
|