SOLVED
Home

Sum returns 0

Highlighted
Whitney Andersen
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". excel help.PNG

7 Replies

Re: Sum returns 0

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!

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

Related Conversations