Conditional count

[Shanmugam]

Hi,

I am having range of cells, that i have to count with specified condition. Below is the example:

CELL DATA
A1 P
A2 P
A3 HDSL
A4 P
A5 AB

The above is to count,
no. of "P" (Present),
no. of "AB" (Absent) and
no. of "HDSL" (Half Day SL).

By using countif formula, we would know how many "P" (the above countif "P" would give 3). But if there is "HDSL", i would like to count HDSL as "0.5".
i.e. The result should be P=3, AB=1,HDSL=0.5, The total present is 3.5

Can you please help me, how to do this?

Thanks,
Shanmugam

[GTO]

Unless I'm missing something (quite possible w/as late as it is), and you are looking for a formula, wouldn't below work?

=COUNTIF(A1:A5,"P") + COUNTIF(A1:A5,"HDSL")/2 + COUNTIF(A1:A5,"AB")

[Shanmugam]

It works. Thanks for this.

[Bob Phillips]

Another way

=SUMPRODUCT(--(LOOKUP(A1:A5,{"AB","HDSL","P"},{1,0.5,1})))

[Bob Phillips]

Yet another

=SUM(--(COUNTIF(A1:A5,{"AB","HDSL","P"})*({1,0.5,1})))