COUNTIF Help

[raymaster98]

Using Excel 2003.

In column D, I have a list of radiology procedures Example (NM HIDA SCAN). All procedures begin with "NM".
Column H lists if the patient was a "NO SHOW". I would like to count the number of procedures in column D that have a corresponding "NO SHOW" in column H. Any help would be appreciated. Thanks, Kenny

 

[Haseeb Avarakkan]

Try;

=SUMPRODUCT(--(D2:D1000<>""),--(H2:H1000="No Show"))

Do not use the whole column reference, D:D, H:H.

 

[raymaster98]

Haseeb,

Thanks for the help. However, that formula counts all instances of "NO SHOWS" in column H. I need to count only the 'NO SHOWS" in column H that correspond with a procedure that has a prefix of "NM" in column D. The list in column D has multiple procedures. Only some are prefixed with "NM". The rest have other prefixes and cannot be included. Hope that makes sense. Thanks, Kenny

 

[Haseeb Avarakkan]

Ok, use this,

=SUMPRODUCT(--(LEFT(D2:D1000,2)="NM"),--(H2:H1000="No Show"))

 

[raymaster98]

Works perfectly. Thanks Haseeb.