If Function

Oli2509

New Member
Joined
Aug 2, 2018
Messages
5
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,
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
3,953
Office Version
  1. 365
Platform
  1. MacOS
=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 ,"")
 
Last edited:

Oli2509

New Member
Joined
Aug 2, 2018
Messages
5
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:

StatusOn WayOn_Site
On Way Coroi11
On Site MCB11
Not Reachable
On Way Eclosia11
P
P
On Way Terra11
Local
Not Reachable
P
Local
Local
Local
Local
On Way Terra11
On Site MCB11
On Site Eclosia11
On Way CJCO11
On Way Darne11

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

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
3,953
Office Version
  1. 365
Platform
  1. MacOS
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 ,"")
 

Watch MrExcel Video

Forum statistics

Threads
1,109,466
Messages
5,528,968
Members
409,848
Latest member
Blomsten
Top