SOLVED
Home

Need help with excel formula

%3CLINGO-SUB%20id%3D%22lingo-sub-897794%22%20slang%3D%22en-US%22%3ENeed%20help%20with%20excel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-897794%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20created%20formulas%20that%20generate%20a%20number%20based%20on%20the%20value%20in%20another%20cell.%26nbsp%3B%20What%20I%20can't%20figure%20out%20how%20to%20do%20is%20then%20create%20a%20formula%20to%20add%20the%20generated%20numbers.%26nbsp%3B%20Can%20someone%20please%20help%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECell%20n2%20has%20the%20formula%3A%26nbsp%3B%26nbsp%3B%3DIF(SUM(F2)%26gt%3B%3D%20220%2C%20%220%22%2C%20IF(SUM(F2)%26gt%3B%3D214%2C%20%221%22%2C%20IF(SUM(F2)%26gt%3B%3D208%2C%20%222%22%2C%20IF(SUM(F2)%26gt%3B%3D0%2C%20%223%22))))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECell%20o2%20has%20the%20formula%3A%26nbsp%3B%26nbsp%3B%3DIF(SUM(H2)%26gt%3B%3D%20220%2C%20%220%22%2C%20IF(SUM(H2)%26gt%3B%3D214%2C%20%221%22%2C%20IF(SUM(H2)%26gt%3B%3D208%2C%20%222%22%2C%20IF(SUM(H2)%26gt%3B%3D0%2C%20%223%22))))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECell%20p2%20has%20the%20formula%3A%26nbsp%3B%26nbsp%3B%3DIF(SUM(K2)%26gt%3B%3D%203%2C%20%220%22%2C%20IF(SUM(K2)%26gt%3B%3D2%2C%20%222%22%2C%20IF(SUM(K2)%26gt%3B%3D1%2C%20%223%22)))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECell%20q2%20has%20the%20formula%3A%26nbsp%3B%26nbsp%3B%3DIF(SUM(L2)%26gt%3B%3D%20220%2C%20%220%22%2C%20IF(SUM(L2)%26gt%3B%3D214%2C%20%221%22%2C%20IF(SUM(L2)%26gt%3B%3D208%2C%20%222%22%2C%20IF(SUM(L2)%26gt%3B%3D0%2C%20%223%22))))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20cell%20r2%20to%20add%20the%20values%20from%20n2%3Aq2.%26nbsp%3B%20Putting%20is%20the%20typical%20sum%20formula%20is%20giving%20me%20a%20value%20of%20zero%20each%20time.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThough%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-897794%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-897826%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20with%20excel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-897826%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F421550%22%20target%3D%22_blank%22%3E%40dawnbear%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat%20since%20you%20return%20texts%20by%20your%20initial%20formulas%2C%20not%20numbers.%20SUM()%20ignores%20any%20texts.%3C%2FP%3E%0A%3CP%3EUse%20something%20like%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DIF(F2%26gt%3B%3D%20220%2C%200%2C%20IF(F2%26gt%3B%3D214%2C%201%2C%20IF(F2%26gt%3B%3D208%2C%202%2C%20IF(F2%26gt%3B%3D0%2C%203%2C%20-1))))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-898434%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20with%20excel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-898434%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F421550%22%20target%3D%22_blank%22%3E%40dawnbear%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20don't%20want%20to%20revise%20your%20formulas%20in%20N2%3AQ2%2C%20you%20may%20use%20any%20of%20these%20formulas%20in%20R2%3A%26nbsp%3B%3C%2FP%3E%3CP%3E1.%20Array%20formula%20(entered%20with%20Ctrl%2BShift%2BEnter)%3A%20%3DSUM(--N2%3AQ2)%3C%2FP%3E%3CP%3E2.%20Non-array%20formula%3A%20%3DSUMPRODUCT(--N2%3AQ2)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-899977%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20with%20excel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-899977%22%20slang%3D%22en-US%22%3EThe%20non-array%20formula%20worked.%20Thanks%20so%20much!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-900060%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20with%20excel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-900060%22%20slang%3D%22en-US%22%3EYou%E2%80%99re%20very%20much%20welcome.%3C%2FLINGO-BODY%3E
dawnbear
New Contributor

I have created formulas that generate a number based on the value in another cell.  What I can't figure out how to do is then create a formula to add the generated numbers.  Can someone please help?

 

Cell n2 has the formula:  =IF(SUM(F2)>= 220, "0", IF(SUM(F2)>=214, "1", IF(SUM(F2)>=208, "2", IF(SUM(F2)>=0, "3"))))

 

Cell o2 has the formula:  =IF(SUM(H2)>= 220, "0", IF(SUM(H2)>=214, "1", IF(SUM(H2)>=208, "2", IF(SUM(H2)>=0, "3"))))

 

Cell p2 has the formula:  =IF(SUM(K2)>= 3, "0", IF(SUM(K2)>=2, "2", IF(SUM(K2)>=1, "3")))

 

Cell q2 has the formula:  =IF(SUM(L2)>= 220, "0", IF(SUM(L2)>=214, "1", IF(SUM(L2)>=208, "2", IF(SUM(L2)>=0, "3"))))

 

I need cell r2 to add the values from n2:q2.  Putting is the typical sum formula is giving me a value of zero each time. 

 

Though

 

4 Replies

@dawnbear 

That since you return texts by your initial formulas, not numbers. SUM() ignores any texts.

Use something like

=IF(F2>= 220, 0, IF(F2>=214, 1, IF(F2>=208, 2, IF(F2>=0, 3, -1))))

 

Solution

@dawnbear 

If you don't want to revise your formulas in N2:Q2, you may use any of these formulas in R2: 

1. Array formula (entered with Ctrl+Shift+Enter): =SUM(--N2:Q2)

2. Non-array formula: =SUMPRODUCT(--N2:Q2)

The non-array formula worked. Thanks so much!
You’re very much welcome.
Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Edge insider Dev bypasses IE mode website list
HotCakeX in Enterprise on
4 Replies