Formula Help

[V_Ernst]

My range is B4:U4. Cells may contain the letter "P", the letter "F", the
letters "NR" or they are blank.

I want to return a "1" if there is a "P", the number "0" if there is a "F"
or an "NR" and then the total number of "P" and the total number of "F " and
"NR"

Thanks!

 

[Gary''s Student]

For a single item, say, B4:

=LOOKUP(B4,{"F","NR","P",""},{1,0,0,""})


to count the "P"

=COUNTIF(B4:U4,"F")

to count the "F" and "NR"

=COUNTIF(B4:U4,"F")+COUNTIF(B4:U4,"NR")

 

[Ashish Mathur]

Hi,

In B5, enter the following formula:

=If(B4="P",1,0) and copy this till U5. To get the total of P's use
=countif(B4:U4,"P"). To get the total of "F" and "NR", use
=countif(B4:U4,"F")+=countif(B4:U4,"NR")

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com