Forum Discussion

shetzel's avatar
shetzel
Brass Contributor
Nov 18, 2020

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.

 

Thanks in advance.  Scott

excel

3 Replies

  • MindreVetande's avatar
    MindreVetande
    Iron Contributor
    Nov 18, 2020
    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's avatar
        MindreVetande
        Iron Contributor
        Nov 18, 2020

        shetzel 

        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

Resources