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.


[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Column A[/TD]
[TD="width: 64"]Column B[/TD]
[/TR]
[TR]
[TD="class: xl333"]a[/TD]
[TD="class: xl333"]1[/TD]
[/TR]
[TR]
[TD="class: xl333"]b[/TD]
[TD="class: xl333"]2[/TD]
[/TR]
[TR]
[TD="class: xl333"]c[/TD]
[TD="class: xl333"]3[/TD]
[/TR]
[TR]
[TD="class: xl333"]a[/TD]
[TD="class: xl333"][/TD]
[/TR]
[TR]
[TD="class: xl333"]a[/TD]
[TD="class: xl333"][/TD]
[/TR]
[TR]
[TD="class: xl333"]a[/TD]
[TD="class: xl333"]5[/TD]
[/TR]
</tbody>[/TABLE]

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.