SOLVED

Sum returns 0

Copper Contributor

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

9 Replies

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!

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

It should work!

Let me know what its result!

Is it 0 or something else?

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.

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

best response confirmed by Whitney Andersen (Copper Contributor)
Solution

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.

 

YOU ARE A GENIUS!! THANK YOU

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

Hi Jose,

 

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

1 best response

Accepted Solutions
best response confirmed by Whitney Andersen (Copper Contributor)
Solution

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.

 

View solution in original post