COUNTIF - more than one condition

[Gary]

Hi All,

I have to count Casual Leaves, Sick Leaves, Privilege Leaves and Unscheduled
Leaves for an employee. In the attendance sheet, they're marked as CL, SL,
PL, UL.

Now how do I get a count of all the leaves with just one formula. Right now
I am using this.
=COUNTIF(A2:Z2,"CL")+COUNTIF(A2:Z2,"SL")+COUNTIF(A2:Z2,"PL")+COUNTIF(A2:Z2,"UL")

Is there an easier way? like multiple conditions with just one COUNTIF?

Thanks
Gary.

 

[Peo Sjoblom]

Try

=SUM(COUNTIF(A2:Z2,{"CL";"PL";"SL";"UL"}))

 

[Guest]

ASSUMING (big assumption), that you only want to count the entries where the
second letter is an L, this is an arrya formula that you enter by pressing
CTRL-SHIFT-ENTER

=SUMPRODUCT(--(MID(A2:Z2,2,1)="L")

 

[Gary]

hey peo. that worked.

a little more help. now if I also want to add half day which is mentioned as
H/D..but I dont want to count it as 1, I want to count it as .5

so if there is one CL and one H/D, the total should be 1.5

any idea?

 

[Peo Sjoblom]

I would add a separate countif

=SUM(COUNTIF(A2:Z2,{"CL";"PL";"SL";"UL"}))+(COUNTIF(A2:Z2,"H/D")/2)


--
Regards,

Peo Sjoblom