If Function

[Oli2509]

Hello All,

Can someone please help me on a sheet that i am working on for a headcount, i have several arguments like Present, Local Leave, not reachable etc and i am using if function e.g: "=IF(C2="On Way xxxxx",1,"") and "=IF(C2="On Site XXXX",1,"") whereas "XXXX" is a different place and same is not working, can someone please help me.

Thanks,

 

[etaf]

=IF( OR(Left(C2,6)="on way", Left(C2,7)="on site") , 1 ,"")

is ON a common text for where you want a 1

=IF( Left(C2,2)="on" , 1 ,"")

 

[Oli2509]

Dear Wayne,

Thank you for the formula, however i got one column for On Site and the other one for on way as and when i'm trying the formula i am having the same counts.

please refer to below:

Status	On Way	On_Site
On Way Coroi	1	1
On Site MCB	1	1
Not Reachable
On Way Eclosia	1	1
P
P
On Way Terra	1	1
Local
Not Reachable
P
Local
Local
Local
Local
On Way Terra	1	1
On Site MCB	1	1
On Site Eclosia	1	1
On Way CJCO	1	1
On Way Darne	1	1

<colgroup><col><col span="2"></colgroup><tbody>
</tbody>

 

[etaf]

then you need a different formula

in the on way column use
=IF( Left(C2,6)="on way" , 1 ,"")

in the on site column
=IF( Left(C2,7)="on site" , 1 ,"")