| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting with multiple matching criteria
Thanks Biff
-- Gary''s Student - gsnu200858 "T. Valko" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| counting matching multiple entries | Excel Discussion (Misc queries) | |||
| MAX value matching multiple criteria | Excel Worksheet Functions | |||
| return multiple records matching multiple criteria | Excel Worksheet Functions | |||
| Counting Cells with multiple criteria.One criteria supporting wild | Excel Worksheet Functions | |||
| Counting Cells with multiple criteria.One criteria supporting wild | Excel Worksheet Functions | |||
Hybrid Mode
