Adding IF Statements in a FORMULA

I am trying to add several people columns together based on their associated statuses to make a staff rota.

Its 90% done and working very well so far.

However, I am trying to create a formula whereby if a person’s status is “Working” its added as a number so I can count number of staff working on a given day.

For example, this basic IF statement works well:
IF({< S. AN}=“Working”, “1”, “0”) …where {<S. AN} is the status column.
This returns a 1 or a 0 based on the status. Great.

However, I want to add the total number of these horizontally across the board for multiple staff.
So for example something like:

SUM (IF({< S. AN}=“Working”, “1”, “0”), IF({< S. AC}=“Working”, “1”, “0”))
this should return “2” if both statuses are titled “Working”. Or “1” if just one is “Working” etc.
However, I can’t seem to get my head around adding these IF statements together?

Any advice appreciated!



You’ve almost got it. However, when you put numbers inside quotes they are treated like strings. So, just remove your quotes. Like this:

SUM(IF({< S. AN}=“Working”, 1, 0), IF({< S. AC}=“Working”, 1, 0))

Damn, I was so close (yet, far away). Thanks for explaining @JCorrell ! Works a treat now!

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.