Counting with multiple matching criteria

G

Gary''s Student

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??
 
J

Jacob Skaria

Try
=SUMPRODUCT(COUNTIF(G7:G3147,Z1:Z4))

=SUM(COUNTIF(G7:G3147,Z1:Z4))
'is an array formula..

If this post helps click Yes
 
G

Gary''s Student

Thanks!
--
Gary''s Student - gsnu200858


Jacob Skaria said:
Try
=SUMPRODUCT(COUNTIF(G7:G3147,Z1:Z4))

=SUM(COUNTIF(G7:G3147,Z1:Z4))
'is an array formula..

If this post helps click Yes
 
S

Shane Devenshire

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.
 
R

Rick Rothstein

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.
 
T

T. Valko

Listed in order of efficiency:

=COUNTIF(A:A,B1)+COUNTIF(A:A,C1)+COUNTIF(A:A,D1)+COUNTIF(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))
 
T

T. Valko

=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.

TRANSPOSE requires array entry.
 
R

Rick Rothstein

I was thinking that SUMPRODUCT, being an array-processing function, would
have imposed the array processing on the elements in the formula.
 
G

Gary''s Student

Thanks Rick
--
Gary''s Student - gsnu200858


Rick Rothstein said:
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.
 
G

Gary''s Student

Thanks Biff
--
Gary''s Student - gsnu200858


T. Valko said:
Listed in order of efficiency:

=COUNTIF(A:A,B1)+COUNTIF(A:A,C1)+COUNTIF(A:A,D1)+COUNTIF(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))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top