CODEWITHSUNDEEP
excelexcel-formula
xtina1231
xtina1231

Reputation: 125

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

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.

Upvotes: 0

Views: 635

Answers (2)

brettdj
brettdj

Reputation: 55682

to get 3 in a single shot

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

Upvotes: 1

Dane I
Dane I

Reputation: 742

Try these:

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

Upvotes: 2

Related Questions