# Counting with multiple matching criteria

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,"pending
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
.

## Relevant Pages

• RE: Help with formula
... Criteria) function. ... would count the occurrences of Email in the range B1:B20 ... Kevin Backmann ... "Mike Busch" wrote: ...
(microsoft.public.excel.misc)
• Re: sumproduct problem
... Arvi Laanemets ... (My real mail address: arvi.laanemetstarkon.ee) ... I am trying to count the number of occurrences of a set of ... but with one of the criteria including 2 ...
(microsoft.public.excel.worksheet.functions)
• Re: Countif/And Function
... > I am trying to count the number of occurrences in a column based on two ... > criteria. ... > count the number of cells in column E with a value of N in column E AND a ... Prev by Date: ...
(microsoft.public.excel.misc)
• Countif/And Function
... I am trying to count the number of occurrences in a column based on two ... criteria. ... count the number of cells in column E with a value of N in column E AND a ... Prev by Date: ...
(microsoft.public.excel.misc)
• Alternative criteria selection
... Using DCOUNTA works like a charm finding the number of occurrences of ... where the entries are the occurrences of ?c?. Can I do this without ... setting up a table for criteria i.e. can I replace the criteria in ...
(microsoft.public.excel.misc)