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:

[TABLE="width: 405"]
<colgroup><col><col span="2"></colgroup><tbody>[TR]
[TD]Status[/TD]
[TD]On Way[/TD]
[TD]On_Site[/TD]
[/TR]
[TR]
[TD]On Way Coroi[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]On Site MCB[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Not Reachable[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]On Way Eclosia[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]P[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]P[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]On Way Terra[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Local[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Not Reachable[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]P[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Local[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Local[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Local[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Local[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]On Way Terra[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]On Site MCB[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]On Site Eclosia[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]On Way CJCO[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]On Way Darne[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

 

[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 ,"")