Ask Your Question

SUM and SUMIF giving unexpected results

asked 2017-03-15 22:06:22 +0200

joea gravatar image

Simple SUM and SUMIF giving odd results.

For the same sheet,

SUMIF should add up C1 thru C100 if E1:E100 contains "text". Ex - "=SUMIF(E1:E100, "text", C1:C100)"

SUM should add up C1 thu C100. Ex- "=SUM(C1:C100)"

SUMIF gives me a figure much larger than expected. SUM gives me a figure much smaller than SUMIF. This is odd since there are significantly fewer E cells containing "text".

I'm at a loss at the moment. Needless to say, I am not very familiar with Calc.

edit retag flag offensive close merge delete


Oh, and why, oh why, does my Captcha present me with languages I do not easily comprehend? My language preference is set to English.

joea gravatar imagejoea ( 2017-03-15 22:08:19 +0200 )edit

OK, so SUMIF is my error in expectation. Some testing and old fashioned calculator proved that. But I am still baffled as to why a simple SUM is so much smaller.

joea gravatar imagejoea ( 2017-03-15 22:42:25 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2017-03-15 22:41:11 +0200

Lupp gravatar image

If a SUM is smaller than expected, the reason sometimes is that cell contents looking like numbers actually are texts. Another reason may be badly formatted negative numbers looking like positive ones. Without a sheet demonstrating the issue I cannot tell for sure. I just can confirm that my LibO does not suffer from that disease.

edit flag offensive delete link more


Thanks. Classic case of brain disconnected from reality. Your mentioning formatting caused me to realize the negative values should not have been included in the sum.
Thanks again.

joea gravatar imagejoea ( 2017-03-15 23:14:20 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2017-03-15 22:06:22 +0200

Seen: 87 times

Last updated: Mar 15 '17