Count number of cells with certain criteria

Unfortunately I need to use excel to solve an issue.

I have a (huge) sheet with many columns. I need to create a summary of some sort.

What I need to know is how to count the number of cells (in a column) with certain criteria.

e.g:

``````              COL1       COL2     COL3
----------------------------------------
ROW1          apple      food       AB
ROW2          apple      food       AB
----------------------------------------
``````

In the case above, if the criteria is apple,food,AB it would return 2. (the criteria will mainly be string matching inside the cell)

-

migrated from stackoverflow.comOct 21 '12 at 19:52

This question came from our site for professional and enthusiast programmers.

you can try to use the `=COUNTIF`

here are some tutorials with explanations

http://office.microsoft.com/en-us/excel-help/countif-HP005209029.aspx

http://blog.contextures.com/archives/2012/01/31/use-excel-countifs-to-count-with-multiple-criteria/

-
yes COUNTIF can be used, but I need to use more than one criteria. – theAlse Oct 20 '12 at 22:44
You didn't mention which version of Excel you are using. `=COUNTIFS` (note trailing `S`) matches multiple criteria and is available in 2007 & later. – andy holaday Oct 20 '12 at 22:49
oh great, just what I was looking for. thanks! – theAlse Oct 20 '12 at 22:54
if you are referring to Andy Holaday's `COUNTIFS` then you shouldn't have accepted this post, you should ask Andy to post a separate response – brettdj Oct 20 '12 at 23:18

In Excel 2007 onwards

``````=COUNTIFS(A1:A2,"food",B1:B2,"apple",C1:C2,"AB")
``````
-