If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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,"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 |
#2
|
|||
|
|||
Counting with multiple matching criteria
If you array-enter (CSE) your formula, it gives you the results.. I think. or use: =SUMPRODUCT(COUNTIF(G7:G3147,K1:K4)) -- NBVC Where there is a will there are many ways. ------------------------------------------------------------------------ NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=109662 |
#3
|
|||
|
|||
Counting with multiple matching criteria
Try
=SUMPRODUCT(COUNTIF(G7:G3147,Z1:Z4)) =SUM(COUNTIF(G7:G3147,Z1:Z4)) 'is an array formula.. If this post helps click Yes --------------- Jacob Skaria "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 |
#4
|
|||
|
|||
Counting with multiple matching criteria
Thanks!
-- Gary''s Student - gsnu200858 "NBVC" wrote: If you array-enter (CSE) your formula, it gives you the results.. I think. or use: =SUMPRODUCT(COUNTIF(G7:G3147,K1:K4)) -- NBVC Where there is a will there are many ways. ------------------------------------------------------------------------ NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=109662 |
#5
|
|||
|
|||
Counting with multiple matching criteria
Thanks!
-- Gary''s Student - gsnu200858 "Jacob Skaria" wrote: Try =SUMPRODUCT(COUNTIF(G7:G3147,Z1:Z4)) =SUM(COUNTIF(G7:G3147,Z1:Z4)) 'is an array formula.. If this post helps click Yes --------------- Jacob Skaria "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 |
#6
|
|||
|
|||
Counting with multiple matching criteria
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 |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
Counting with multiple matching criteria
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 |
#9
|
|||
|
|||
Counting with multiple matching criteria
Listed in order of efficiency:
=COUNTIF(A:A,B1)+COUNTIF(A:A,C1)+COUNTIF(A:A,D1)+C OUNTIF(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)) -- Biff Microsoft Excel MVP "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 |
#10
|
|||
|
|||
Counting with multiple matching criteria
=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. -- Biff Microsoft Excel MVP "Rick Rothstein" wrote in message ... 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 |
|
Thread Tools | |
Display Modes | |
|
|