Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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)

share|improve this question

migrated from stackoverflow.com Oct 21 '12 at 19:52

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

up vote 1 down vote accepted

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://spreadsheets.about.com/od/somethingiffunctions/ss/2011-04-16-Excel-2010-Countif-Function-Step-By-Step-Tutorial.htm

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

share|improve this answer
    
yes COUNTIF can be used, but I need to use more than one criteria. – theAlse Oct 20 '12 at 22:44
3  
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")
share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .