Thanks Rick
--
Gary''s Student - gsnu200858


"Rick Rothstein" wrote:

I'm assuming that there are more entry possibilities than the four you
listed and that she wants to count only those four items from among all the
possible entries (otherwise a simple COUNTA function call would work). Using
the concept Shane posted, but modifying it for the search items to be listed
in a column (J1:J4 in my formula) rather than a row (W1:Z1 in Shane's
formula), this array-entered formula should work...

=SUM(--(G$1:G$100=TRANSPOSE(J1:J4)))

I found it interesting that using SUMPRODUCT instead of SUM did not work
correctly unless you array-entered it.

--
Rick (MVP - Excel)


"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