Ifs function

Copper Contributor

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.

 

Thanks in advance.  Scott

3 Replies
This will give 0.5 if B4 is 301 or 302 otherwise 1
=SWITCH(B4,301,0.5,302,0.5,1)
Multiply with your sumsif
=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)

@MindreVetande 

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

@Scott Hetzel 

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