• 129K Members
• 1,461 Online
• 32.1K Conversations
SOLVED

Highlighted
New Contributor

## Sum returns 0

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

## Re: Sum returns 0

It's a common issue!

Quick solution:

`=SUMPRODUCT(VALUE(SUM RANGE))`

## Re: Sum returns 0

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

## Re: Sum returns 0

It should work!

Let me know what its result!

Is it 0 or something else?

## Re: Sum returns 0

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.

## Re: Sum returns 0

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

Solution

## Re: Sum returns 0

Make sure to enter the second formula by pressing Ctrl+Shift+Enter as I mentioned before, this keystroke shortcut will force the formula to deliver the right result.

Give it a try.

## Re: Sum returns 0

YOU ARE A GENIUS!! THANK YOU

## Re: Sum returns 0

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

## Re: Sum returns 0

Hi Jose,

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

Related Conversations