Count missing data

  • Thread starter Thread starter Ruan
  • Start date Start date
R

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
 
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
 
Thanks for your help that works great. I have never seen the brackets { }
used before. What is the difference?

Ruan
 
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)
 
Yours might be a little better Peo, considering mine only takes *one* space
into consideration.
 
Ruan said:
... =SUMPRODUCT((A2:A100={" ",""})*(B2:B100="a"))
.. I have never seen the brackets { }
used before. What is the difference?
Click to expand...

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