PDA

View Full Version : Conditional count



Shanmugam
09-30-2008, 04:56 AM
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
09-30-2008, 05:59 AM
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
09-30-2008, 07:10 AM
It works. Thanks for this.

Bob Phillips
09-30-2008, 07:23 AM
Another way

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

Bob Phillips
09-30-2008, 07:23 AM
Yet another

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