SOLVED

Need help with excel formula

Copper 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))))

 

best response confirmed by dawnbear (Copper Contributor)
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.
1 best response

Accepted Solutions
best response confirmed by dawnbear (Copper Contributor)
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)

View solution in original post