• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 244
  • Last Modified:

Multiple criteria using COUNT and IF

Hi
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.

Thanks

testcountif.xls
0
alam747
Asked:
alam747
  • 4
  • 3
1 Solution
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

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

=SUMPRODUCT(--(NOT(ISERROR(FIND("best001",Status!$A$1:$A$38)))),--(Status!$B$1:$B$38="Approved"))

cheers, teylyn
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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


Alpha"best001"

then you can also use

=SUMPRODUCT(--(Status!$A$1:$A$38="Alpha"&"""best001"&""""),--(Status!$B$1:$B$38="Approved"))

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

cheers, teylyn

0
 
alam747Author Commented:
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.

Thanks
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
alam747Author Commented:
Sorry, I will try the way you advised, and let you know if I face any problem. Please ignore my previous comment.
Thanks
0
 
alam747Author Commented:
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.

Thanks
0
 
Rory ArchibaldCommented:
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: http://www.contextures.com/xlNames01.html#Dynamic

Regards,
Rory
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

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

=SUMPRODUCT(--(NOT(ISERROR(FIND("best001",Company)))),--(Status="Approved"))

cheers, teylyn
testcountif.xls
0
 
alam747Author Commented:
Thanks a lot, it helps....
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now