# Excel 2010 Multiple Criteria Count

Data is arranged as per the table in the image below. Need to summarize data based on the individual criteria on a daily basis.

-
Can you possibly post a table that has some numeric values and mock up the expected result? it is not apparent where the formula is supposed to go and where the data to be summed is coming from. – teylyn Jan 23 '13 at 7:55
Hope this gives better idea.... – roman_bull Jan 23 '13 at 8:36

You can do this with simple `COUNTIFS`:

C12: `=COUNTIFS(\$B\$3:\$B\$8,\$B12;C\$3:C\$8,"GS")+COUNTIFS(\$B\$3:\$B\$8,\$B12;C\$3:C\$8,"HD")`

C13: `=COUNTIFS(\$B\$3:\$B\$8,\$B13;C\$3:C\$8,"GS")+COUNTIFS(\$B\$3:\$B\$8,\$B13;C\$3:C\$8,"HT")`

C14: `=COUNTIFS(\$B\$3:\$B\$8,\$B14;C\$3:C\$8,"CS")`

If you have a lot of criteria, you can use an array formula instead:

In A12, store the list of criteria, separated by comma, i.e. `GS, HD`. Same for A13, A14, etc. Then, in C12, use the following formula:

```=SUM((\$B\$2:\$B\$8=\$B12)*(IFERROR(SEARCH(" "&C\$2:C\$8&","," "&\$A12&","),0)0))
```

Enter this as an array formula, i.e. press Ctrl-Shift-Enter.

-
There are about 20 criteria like GS, HD, LP, etc. This definitely did work but wishing if there is any shorter formula... – roman_bull Jan 23 '13 at 9:07
Thank you Peter Albert – roman_bull Jan 23 '13 at 10:00
Next time, please tell this in advance! I adjusted the answer, as there is another way. – Peter Albert Jan 23 '13 at 10:39
Never mind, it was just one time effort to create the formula and it is working fine...!! Thank you once again Peter Albert – roman_bull Jan 23 '13 at 10:58
If you put the 20 criteria in separate cells, e.g. Z1:Z20 you can use COUNTIFS like this: `=SUMPRODUCT(COUNTIFS(\$B\$3:\$B\$8,\$B12;C\$3:C\$8,Z1:Z20))` – barry houdini Jan 23 '13 at 12:14