HELP!!: Conditional COUNTA formula?????

[mchsueh87]

hi, i am searching everywhere one the internet for this. It doesnt seem quite work. In column A i have various status and in column B, i have various values with blanks. I want to count the values in column B (not blank) that has corresponding status in column A. see below for example.

Column A	Column B
a	1
b	2
c	3
a
a
a	5

<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>

So if column A ="a", i want to count the values in column B. In this case, not counting the blanks, the cell would return 2.

i think the answer is either sumproduct or sum(if( statements. I couldnt get it to work though

Please help me!!

-Jay

 

[Jonmo1]

Which version of Excel do you have?

In XL2007 or higher, we have COUNTIFS
=COUNTIFS(A1:A10,"a",B1:B10,"<>")

In XL2003 you need SUMPRODUCT
=SUMPRODUCT(--(A1:A10="a"),--(B1:B10<>""))


Hope that helps.

 

[mchsueh87]

thanks! countifs works perfectly

 

[Jonmo1]

Glad to help, thanks for the feedback.