Counting with multiple matching criteria
- From: Gary''s Student <GarysStudent@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Wed, 24 Jun 2009 06:45:03 -0700
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
.
- Follow-Ups:
- Re: Counting with multiple matching criteria
- From: T. Valko
- Re: Counting with multiple matching criteria
- From: Rick Rothstein
- RE: Counting with multiple matching criteria
- From: Shane Devenshire
- RE: Counting with multiple matching criteria
- From: Jacob Skaria
- Re: Counting with multiple matching criteria
- From: NBVC
- Re: Counting with multiple matching criteria
- Prev by Date: Modification Time Update
- Next by Date: Re: give each NO in a drop down a different value
- Previous by thread: Modification Time Update
- Next by thread: Re: Counting with multiple matching criteria
- Index(es):
Relevant Pages
|
