Home

Multiple IF'S

%3CLINGO-SUB%20id%3D%22lingo-sub-632263%22%20slang%3D%22en-US%22%3EMultiple%20IF'S%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-632263%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20been%20trying%20to%20do%20this%20for%20ages!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20got%20three%20cells%20on%20worksheet%201%20and%20if%26nbsp%3Bany%20of%20them%26nbsp%3Bdrop%20below%2060%2C%20I%20want%20another%26nbsp%3B%20single%20cell%20on%20worksheet%203%20to%20increase%20by%201.%20I've%20been%20trying%20this%20formula%3A-%3C%2FP%3E%3CP%3E%3DIF(Sheet1!D2%26lt%3B60%2C1%2C0)%26amp%3BIF(Sheet1!D3%26lt%3B60%2C1%2C0)%3C%2FP%3E%3CP%3Ebut%20instead%20of%20changing%20to%202%20if%202%20cells%20drop%20below%2060%2C%20it%20displays%2011.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20tips%20where%20I'm%20going%20wrong%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance%20of%20any%20suggestions!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-632263%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-632277%22%20slang%3D%22en-US%22%3ERe%3A%20Multiple%20IF'S%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-632277%22%20slang%3D%22en-US%22%3EHello!%20Replace%20the%20%E2%80%9E%26amp%3B%E2%80%9C%20in%20your%20formula%20with%20a%20%E2%80%9E%2B%E2%80%9C.%20The%20%26amp%3B%20is%20a%20sign%20for%20excel%20to%20take%20your%20if%20results%20as%20a%20text%20concatenation.%20So%20it%20makes%2011%2C%20not%202.%20All%20the%20best%2C%20Eva%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-632337%22%20slang%3D%22en-US%22%3ERe%3A%20Multiple%20IF'S%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-632337%22%20slang%3D%22en-US%22%3EIf%20the%20cells%20from%20which%20you%20want%20to%20evaluate%20whether%20any%20of%20them%20is%20below%2060%20are%20in%20Sheet1!D2%3AD4%20and%20the%20cell%20you%20want%20to%20increase%20by%201%20is%20in%20Sheet3!A2%2C%20the%20formula%20in%20Sheet3!B2%20is%3A%3CBR%20%2F%3E%3D(COUNTIF(Sheet1!%24D%242%3A%24D%244%2C%22%26lt%3B%22%26amp%3B60)%26gt%3B0)%2BA2%3CBR%20%2F%3EInstead%20of%20hard-coding%2060%20in%20the%20foregoing%20formula%2C%20I%20suggest%20you%20store%20in%20a%20cell%2C%20such%20as%20Sheet3!B1%2C%20so%20that%20you%20can%20modify%20its%20value%20without%20modifying%20this%20formula%3A%3CBR%20%2F%3E%3D(COUNTIF(Sheet1!%24D%242%3A%24D%244%2C%22%26lt%3B%22%26amp%3BB1)%26gt%3B0)%2BA2%3CBR%20%2F%3ENote%20that%20the%20foregoing%20formula%20returns%20the%20value%20in%20Sheet3!A2%20increased%20by%201%2C%20if%20any%20of%20the%20cells%20in%20Sheet1!%24D%242%3A%24D%244%20is%20below%2060%3B%20otherwise%2C%20it%20returns%20the%20value%20in%20Sheet3!A2%20as%20is.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-634247%22%20slang%3D%22en-US%22%3ERe%3A%20Multiple%20IF'S%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-634247%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F153902%22%20target%3D%22_blank%22%3E%40Eva%20Vogel%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20help%2C%20it%20is%20much%20appreciated!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EKind%20regards%2C%3C%2FP%3E%3CP%3ELee%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-634251%22%20slang%3D%22en-US%22%3ERe%3A%20Multiple%20IF'S%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-634251%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20help%2C%20very%20much%20appreciated!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECheers%2C%3C%2FP%3E%3CP%3ELee%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-634290%22%20slang%3D%22en-US%22%3ERe%3A%20Multiple%20IF'S%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-634290%22%20slang%3D%22en-US%22%3EIt%20seems%20your%20appreciation%20for%20me%20is%20superior%20as%20indicated%20by%20your%20use%20of%20the%20adverb%20%E2%80%9Cvery%E2%80%9D!%20Nonetheless%2C%20I%20want%20to%20know%20if%20my%20suggested%20formula%20returns%20your%20desired%20result.%20It%20behooves%20me%20to%20be%20apprised%20thereof.%3C%2FLINGO-BODY%3E
Stokely77
New Contributor

Hi,

 

I've been trying to do this for ages!

 

I've got three cells on worksheet 1 and if any of them drop below 60, I want another  single cell on worksheet 3 to increase by 1. I've been trying this formula:-

=IF(Sheet1!D2<60,1,0)&IF(Sheet1!D3<60,1,0)

but instead of changing to 2 if 2 cells drop below 60, it displays 11.

 

Any tips where I'm going wrong?

 

Thanks in advance of any suggestions!

5 Replies
Hello! Replace the „&“ in your formula with a „+“. The & is a sign for excel to take your if results as a text concatenation. So it makes 11, not 2. All the best, Eva
If the cells from which you want to evaluate whether any of them is below 60 are in Sheet1!D2:D4 and the cell you want to increase by 1 is in Sheet3!A2, the formula in Sheet3!B2 is:
=(COUNTIF(Sheet1!$D$2:$D$4,"<"&60)>0)+A2
Instead of hard-coding 60 in the foregoing formula, I suggest you store in a cell, such as Sheet3!B1, so that you can modify its value without modifying this formula:
=(COUNTIF(Sheet1!$D$2:$D$4,"<"&B1)>0)+A2
Note that the foregoing formula returns the value in Sheet3!A2 increased by 1, if any of the cells in Sheet1!$D$2:$D$4 is below 60; otherwise, it returns the value in Sheet3!A2 as is.

@Eva Vogel 

Thank you for your help, it is much appreciated!

 

Kind regards,

Lee

@Twifoo 

 

Thank you for your help, very much appreciated!

 

Cheers,

Lee

It seems your appreciation for me is superior as indicated by your use of the adverb “very”! Nonetheless, I want to know if my suggested formula returns your desired result. It behooves me to be apprised thereof.
Related Conversations