turn on suggestions

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

Showing results for

- 157K Members
- 4,123 Online
- 38.5K Conversations

- Home
- :
- Excel
- :
- Formulas and Functions
- :
- Sum returns 0

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

Showing results for

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

Highlighted

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

12-29-2017 10:41 AM

**I have a data validation list in column C. **

**In column D I have this formula:**

=IF(OR(C$5:C$30="400 Confirmation Calls",C$5:C$30="1100 Inbound Calls",C$5:C$30="430 Appts Created"),1,"")&IF(OR(C$5:C$30="420 Confirmation Calls",C$5:C$30="1200 Inbound Calls",C$5:C$30="450 Appts Created"),2,"")&IF(OR(C$5:C$30="450 Confirmation Calls",C$5:C$30="1300 Inbound Calls",C$5:C$30="470 Appts Created"),3,"")

At the bottom of column D I want it to sum up the total for all of D and for some reason it just returns "0".

9 Replies

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

12-29-2017 11:14 AM - edited 12-29-2017 11:16 AM

It's a common issue!

Quick solution:

=SUMPRODUCT(VALUE(SUM RANGE))

Please refer to my answer to this question to learn more about this issue!

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

12-29-2017 11:51 AM

It didnt seem to work... or I did it wrong which is much more probable.

I put

=SUMPRODUCT(VALUE(d5:d29))

I only want it to sum up the range D5:D29

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

12-29-2017 12:11 PM

It should work!

Let me know what its result!

Is it 0 or something else?

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

12-29-2017 12:29 PM

I guess that you have in sum range some blank cells!

If so, please use this workaround:

=SUM(IFERROR(VALUE(sum_range),0))

**NOTE:** to enter this formula, don't press **Enter**, but press **Ctrl+Shift+Enter **simultaneously, and do that each time you open the formula in the edit mode.

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

12-29-2017 02:16 PM

So the first formula you gave me returned the #VALUE! error. The second formula just comes up as 0

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

Best Response confirmed by
Whitney Andersen (New Contributor)

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

01-03-2018 08:30 AM

YOU ARE A GENIUS!! THANK YOU

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

02-12-2018 08:15 PM

sorry to boder but i've been trying to use the same step you gave to solve this problem and it keeps giving my 0. please would you help me

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

02-12-2018 08:28 PM

Hi Jose,

Please post a question in this community and describe your issue in details to figure out how to help you!

Related Conversations

How to count and sum "Condtional formatting" cells by color in Excel 2010?

nova.01
in
Excel
on
10-12-2017
3,116
Views

0 Likes

13 Replies

Share

Popular

Learning Resources

Programs

Values

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