SOLVED

Highlighted
New Contributor

# Need help with excel formula

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
Highlighted

# Re: Need help with excel formula

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

Highlighted
Best Response confirmed by dawnbear (New Contributor)
Solution

# Re: Need help with excel formula

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)

Highlighted

# Re: Need help with excel formula

The non-array formula worked. Thanks so much!
Highlighted

# Re: Need help with excel formula

You’re very much welcome.
Highlighted

# Re: Need help with excel formula

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

Highlighted

# Re: Need help with excel formula

@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

Highlighted

# Re: Need help with excel formula

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.