How to Count Non Blank Cells which Depends on another criterion/column?

0
votes

I need a formula that will count all non blank cells within a range if there is a specific value for another column.

Column A  Column B   Column C
Anne       Jan 16       AO
Tina       Aug 12       VO
Perry      Mar 31       AO
Gab                     AO
Mallows                 VO

With this, i need to count ALL NON-BLANK cells if:
Column C = "AO" Column C = "VO"

I tried this formula but seems to count all including blank cells. 

=COUNTIFS(C:C, "AO", B:B, "<>COUNTBLANK(B2:B5)")
=COUNTIFS(C:C, "VO", B:B, "<>COUNTBLANK(B2:B5)")

The result should be 

If Column C is AO, the result is **2**.
If Column C is VO, the result is **1**.

Can you help me with this? Your prompt response is greatly appreciated.

2
excelexcel-formula

2 Answers

2
votes

Try these:

=COUNTIFS(C:C, "AO",B:B, "<>")
=COUNTIFS(C:C, "VO",B:B, "<>")
1
votes

to get 3 in a single shot

=SUMPRODUCT(--(C1:C5="AO")+(C1:C5="VO"),--(B1:B5<>""))