Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

- 460K Members
- 11.3K Online
- 557K Conversations

- Home
- :
- Excel
- :
- General Discussion
- :
- Need help with excel formula

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

SOLVED
## Need help with excel formula

Conversation Options

- Subscribe to RSS Feed
- Mark Conversation as New
- Mark Conversation as Read
- Pin this Conversation for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-07-2019 12:37 PM

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

Labels:

7 Replies

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-07-2019 12:44 PM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-07-2019 10:00 PM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-03-2019 07:14 PM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-03-2019 07:33 PM

@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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-05-2019 09:02 AM

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

Security Community Webinars

Valon_Kolica
in
Security, Privacy & Compliance
on
10-22-2019
16.1K
Views

12 Likes

13 Replies

flashing a white screen while open new tab

Deleted
in
Discussions
on
10-05-2019
30.8K
Views

14 Likes

14 Replies

How to Prevent Teams from Auto-Launch

chenrylee
in
Microsoft Teams
on
06-27-2019
178K
Views

8 Likes

29 Replies

Stable version of Edge insider browser

HotCakeX
in
Discussions
on
10-12-2019
35K
Views

7 Likes

35 Replies

Share

Popular

Learning Resources

Programs

Values

- Contact us
- Privacy & cookies
- Terms of use
- Trademarks
- About our ads
- © Microsoft