alam747
asked on
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
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
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="Al pha"&"""be st001"&""" "),--(Stat us!$B$1:$B $38="Appro ved"))
The quotation marks in the cell require the unusual formulation of the search string.
cheers, teylyn
Alpha"best001"
then you can also use
=SUMPRODUCT(--(Status!$A$1
The quotation marks in the cell require the unusual formulation of the search string.
cheers, teylyn
ASKER
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
I am using office 2003.Not much familiar of excel formula.
Thanks for your understanding.
Thanks
ASKER
Sorry, I will try the way you advised, and let you know if I face any problem. Please ignore my previous comment.
Thanks
Thanks
ASKER
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
Please advice how to do that.
I appreciate your prompt advice.
Thanks
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks a lot, it helps....
in Excel 2007 and later you would be able to use Countifs, in 2003 and earlier you can use Sumproduct.
=SUMPRODUCT(--(NOT(ISERROR
cheers, teylyn