Forum Discussion
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 ...
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)
=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)
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
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
