Forum Discussion

Whitney Andersen

Copper Contributor

Dec 29, 2017

Solved

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...

Formulas & Functions

Haytham Amairah
Dec 30, 2017
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.

Haytham Amairah

Silver Contributor

Dec 29, 2017

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!

Whitney Andersen

Copper Contributor

Dec 29, 2017

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

Haytham Amairah
Silver Contributor
Dec 29, 2017
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.
- jose cedeño
  Copper Contributor
  Feb 13, 2018
  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
  - Haytham Amairah
    Silver Contributor
    Feb 13, 2018
    Hi Jose,
    
    Please post a question in this community and describe your issue in details to figure out how to help you!
- Whitney Andersen
  Copper Contributor
  Dec 29, 2017
  So the first formula you gave me returned the #VALUE! error. The second formula just comes up as 0
  - Haytham Amairah
    Silver Contributor
    Dec 30, 2017
    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.
Haytham Amairah
Silver Contributor
Dec 29, 2017
It should work!

Let me know what its result!

Is it 0 or something else?

Resources