 # Ifs function

I am trying to use the ifs statement to determine if a cell is resource 301, them use a sumsif, if 302, them use a sumsif and if anything else use a sumsif. When using resource 301 and 302 the labor pool is *2, but the earned hours are * 1 so I want to take half of the total labor hours for my result only for resource 301 and 302. I have attached my spreadsheet so you can see what I have tried. In D4 I tried ending with a true staementand in D5 I tried ending with a false statement.

3 Replies

# Re: Ifs function

This will give 0.5 if B4 is 301 or 302 otherwise 1
=SWITCH(B4,301,0.5,302,0.5,1)
=SWITCH(B4,301,0.5,302,0.5,1)*sumifs(xxxx)
Or, older excel
=IF(or(B4=301,B4=302);0.5,1)
=IF(or(B4=301,B4=302);0.5,1)*sumifs(xxxx)

# Re: Ifs function

I tried adding in the switch formula and it did not divide by 2.  You can see the updated formula in the attached in cell D4.

Thanks

# Re: Ifs function

You can divide if you want to. I don't see that in D4

=SUMIFS('Weekly Data'!\$I:\$I,'Weekly Data'!\$L:\$L,'Hours Relieved'!B4,'Weekly Data'!\$X:\$X,'Hours Relieved'!\$U\$1,'Weekly Data'!\$O:\$O,'Hours Relieved'!\$D\$2)/IF(OR(B4=302,B4=301),2,1)

or multiply

=SUMIFS('Weekly Data'!\$I:\$I,'Weekly Data'!\$L:\$L,'Hours Relieved'!B4,'Weekly Data'!\$X:\$X,'Hours Relieved'!\$U\$1,'Weekly Data'!\$O:\$O,'Hours Relieved'!\$D\$2)*IF(OR(B4=302,B4=301),0.5,1)

Same thing