COUNTIF - more than one condition
 

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(A 2:Z2,"PL")+COUNTIF(A2:Z2,"UL")

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

Thanks
Gary.

COUNTIF - more than one condition
 

Try

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



--
Regards,

Peo Sjoblom



"Gary" wrote in message
...
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(A 2:Z2,"PL")+COUNTIF(A2:Z2,"UL")

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

Thanks
Gary.

COUNTIF - more than one condition
 

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" wrote:

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(A 2:Z2,"PL")+COUNTIF(A2:Z2,"UL")

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

Thanks
Gary.

COUNTIF - more than one condition
 

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" wrote in message
...
Try

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



--
Regards,

Peo Sjoblom



"Gary" wrote in message
...
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(A 2:Z2,"PL")+COUNTIF(A2:Z2,"UL")

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

Thanks
Gary.

COUNTIF - more than one condition
 

I would add a separate countif

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


--
Regards,

Peo Sjoblom




"Gary" wrote in message
...
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" wrote in message
...
Try

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



--
Regards,

Peo Sjoblom



"Gary" wrote in message
...
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(A 2:Z2,"PL")+COUNTIF(A2:Z2,"UL")

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

Thanks
Gary.