Need help with excel formula

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. 




7 Replies


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 (New Contributor)


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:

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.