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

# 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
• 4
• 3
1 Solution

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

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

Author 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

Author 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

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

Thanks
0

Commented:
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

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

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

## Featured Post

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