Count missing data

[Ruan]

Hello,

I would like to count missing data in a Column range due to the criteria in
another column range. The problem I have is that the "Birth Date" column
range has a "vlookup" formula in it, which sometimes formulates a Blank. I
need to count these Blank cells when the Status = "A".

Example:
Birth Date Status
02/03/71 A
A
12/02/58 A
09/15/67 A
A
11/21/79 A

Missing = 2

Thanks
Ruan

 

[RagDyer]

Try this:

=SUMPRODUCT((A2:A100={" ",""})*(B2:B100="a"))
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


Hello,

I would like to count missing data in a Column range due to the criteria in
another column range. The problem I have is that the "Birth Date" column
range has a "vlookup" formula in it, which sometimes formulates a Blank. I
need to count these Blank cells when the Status = "A".

Example:
Birth Date Status
02/03/71 A
A
12/02/58 A
09/15/67 A
A
11/21/79 A

Missing = 2

Thanks
Ruan

 

[Ruan]

Thanks for your help that works great. I have never seen the brackets { }
used before. What is the difference?

Ruan

 

[Peo Sjoblom]

Another way

=SUMPRODUCT(--(TRIM(A2:A100)=""),--(B2:B100="A"))



--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

 

[Ragdyer]

Yours might be a little better Peo, considering mine only takes *one* space
into consideration.

 

[Max]

... =SUMPRODUCT((A2:A100={" ",""})*(B2:B100="a"))
.. I have never seen the brackets { }
used before. What is the difference?

Think the { } is functionally equivalent to an OR(), viz.:
Every cell in A2:A100 is checked to contain either " " or ""