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%3CLINGO-SUB%20id%3D%22lingo-sub-977065%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20with%20excel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-977065%22%20slang%3D%22en-US%22%3E%3CP%3Ei%20am%20looking%20to%20understand%20financial%20formulas%20and%20how%20to%20explain%20them%20to%20a%20class%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-977095%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20with%20excel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-977095%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F439734%22%20target%3D%22_blank%22%3E%40holy90%3C%2FA%3E%26nbsp%3B%26nbsp%3B%20You%20posted%20that%20you're%20%22looking%20to%20understand%20financial%20formulas%20and%20how%20to%20explain%20them%20to%20a%20class%22%20but%20you%20posted%20at%20the%20tail%20end%20of%20a%20string%20on%20a%20different%20subject.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EA%20new%20topic--which%20yours%20is--should%20be%20started%20as%20a%20%22New%20Conversation%22%20where%20you'd%20get%20a%20much%20better%20response.%20Do%20that%20on%20this%20page%3A%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FExcel%2Fbd-p%2FExcelGeneral%22%20target%3D%22_blank%22%3Ehttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FExcel%2Fbd-p%2FExcelGeneral%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-984307%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20with%20excel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-984307%22%20slang%3D%22en-US%22%3EVery%20well%20said.%20Meanwhile%2C%20the%20question%20in%20the%20New%20Conversation%20must%20be%20specific%20to%20somehow%20create%20a%20mental%20vision%20of%20how%20the%20givens%20are%20presented%20and%20how%20the%20likely%20solution%20will%20be%20suggested.%20Preferably%2C%20a%20sample%20Excel%20file%20with%20sample%20output%20must%20be%20attached%20thereto.%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

 

7 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.

i am looking to understand financial formulas and how to explain them to a class 

@holy90   You posted that you're "looking to understand financial formulas and how to explain them to a class" but you posted at the tail end of a string on a different subject.

 

A new topic--which yours is--should be started as a "New Conversation" where you'd get a much better response. Do that on this page: https://techcommunity.microsoft.com/t5/Excel/bd-p/ExcelGeneral

Very well said. Meanwhile, the question in the New Conversation must be specific to somehow create a mental vision of how the givens are presented and how the likely solution will be suggested. Preferably, a sample Excel file with sample output must be attached thereto.
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies