Tell me more ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.
up vote 0 down vote favorite

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

2 Answers

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. – Alborz 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! – Alborz 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
up vote 0 down vote

In excel 2007 onwards, you will get

=COUNTIFS(A1:A2,"food",B1:B2,"apple",C1:C2,"ab")

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.