Multiple criteria using COUNT and IF

Posted on 2011-09-21
Last Modified: 2012-05-12
Would you please check my attached excel sample file where I want to count the total of *Approved* in cell B:B of sheet Status where cell A:A match with the criteria(best001)
Result in cell A2 Sheet1 will be 8(those are colered in Green)
I added formulla in cell A2 only but it gave me 0.
Please advice where need to be change or modified.
It would be great if you update the attached file and send it back to me.
Thanks in advance for your understanding and co-operation.


Question by:alam747
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 36578231

in Excel 2007 and later you would be able to use Countifs, in 2003 and earlier you can use Sumproduct.


cheers, teylyn
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 36578276
The above will find any cell that contains "best001" AND has approved in the next cell. If you want to find an exact match for the text


then you can also use


The quotation marks in the cell require the unusual formulation of the search string.

cheers, teylyn


Author Comment

ID: 36578314
Thanks for your prompt response, would you please send me the excel file with your update.
I am using office 2003.Not much familiar of excel formula.
Thanks for your understanding.

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 36578339
Sorry, I will try the way you advised, and let you know if I face any problem. Please ignore my previous comment.

Author Comment

ID: 36578393
Thanks a lot, it works only one thing I want to do is instead of specific line numbers(as you write $A$1: $A$38 same $B$1:$B38) how can I use like A:A and B:B to count as many lines in cell A and cell B have data to count .
Please advice how to do that.
I appreciate your prompt advice.

LVL 85

Expert Comment

by:Rory Archibald
ID: 36578852
Not for points.

You cannot do that in 2003. You have to specify a range, although you can use $A$2:$A$65536 for example. Better to use dynamic named ranges though:

LVL 50

Accepted Solution

Ingeborg Hawighorst earned 500 total points
ID: 36579051

please find in the attached file

- two dynamic range names for the Company and Status columns in the Status sheet
Company      =Status!$A$1:INDEX(Status!$A:$A,COUNTA(Status!$A:$A))
Status      =OFFSET(Company,0,1)

- an adjusted Sumproduct formula that uses these range names rather than fixed references


cheers, teylyn

Author Closing Comment

ID: 36694108
Thanks a lot, it helps....

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question