Ifs function

%3CLINGO-SUB%20id%3D%22lingo-sub-1901106%22%20slang%3D%22en-US%22%3EIfs%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1901106%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20to%20use%20the%20ifs%20statement%20to%20determine%20if%20a%20cell%20is%20resource%20301%2C%20them%20use%20a%20sumsif%2C%20if%20302%2C%20them%20use%20a%20sumsif%20and%20if%20anything%20else%20use%20a%20sumsif.%20When%20using%20resource%20301%20and%20302%20the%20labor%20pool%20is%20*2%2C%20but%20the%20earned%20hours%20are%20*%201%20so%20I%20want%20to%20take%20half%20of%20the%20total%20labor%20hours%20for%20my%20result%20only%20for%20resource%20301%20and%20302.%20I%20have%20attached%20my%20spreadsheet%20so%20you%20can%20see%20what%20I%20have%20tried.%20In%20D4%20I%20tried%20ending%20with%20a%20true%20staementand%20in%20D5%20I%20tried%20ending%20with%20a%20false%20statement.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance.%26nbsp%3B%20Scott%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1901106%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1901240%22%20slang%3D%22en-US%22%3ERe%3A%20Ifs%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1901240%22%20slang%3D%22en-US%22%3EThis%20will%20give%200.5%20if%20B4%20is%20301%20or%20302%20otherwise%201%3CBR%20%2F%3E%3DSWITCH(B4%2C301%2C0.5%2C302%2C0.5%2C1)%3CBR%20%2F%3EMultiply%20with%20your%20sumsif%3CBR%20%2F%3E%3DSWITCH(B4%2C301%2C0.5%2C302%2C0.5%2C1)*sumifs(xxxx)%3CBR%20%2F%3EOr%2C%20older%20excel%3CBR%20%2F%3E%3DIF(or(B4%3D301%2CB4%3D302)%3B0.5%2C1)%3CBR%20%2F%3E%3DIF(or(B4%3D301%2CB4%3D302)%3B0.5%2C1)*sumifs(xxxx)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1902234%22%20slang%3D%22en-US%22%3ERe%3A%20Ifs%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1902234%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F857441%22%20target%3D%22_blank%22%3E%40MindreVetande%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20tried%20adding%20in%20the%20switch%20formula%20and%20it%20did%20not%20divide%20by%202.%26nbsp%3B%20You%20can%20see%20the%20updated%20formula%20in%20the%20attached%20in%20cell%20D4.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional 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