Turn on suggestions

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

Showing results for

- 412K Members
- 8,274 Online
- 468K 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.

Showing results for

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:

4 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

Related Conversations

Stable version of Edge insider browser

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

1 Likes

35 Replies

flashing a white screen while open new tab

cntvertex
in
Discussions
on
10-05-2019
27.9K
Views

10 Likes

13 Replies

What is Canary ring in Windows insider program? and how do we get them?

HotCakeX
in
Windows Insider Program
on
09-27-2019
11.2K
Views

0 Likes

9 Replies

How to Prevent Teams from Auto-Launch

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

6 Likes

28 Replies

Edge insider Dev bypasses IE mode website list

HotCakeX
in
Enterprise
on
10-01-2019
1,272
Views

0 Likes

4 Replies

Share

Popular

Learning Resources

Programs

Values

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