CODEWITHSUNDEEP
excelexcel-formula
Nick Bull
Nick Bull

Reputation: 9876

SUMIF formula refuses to work

I have a range of values, given below, in column A of my worksheet

635320393516930000.00
635320394621433000.00
635320395928558000.00
635320397215862000.00
635320397973875000.00
635320398849985000.00
635320400627157000.00
635320401276114000.00
635320402391388000.00
635320403478360000.00
635320405195488000.00
635320405862506000.00
635320406509623000.00

However, both of these formulae return a sum of 0:

=SUMIFS(A:A,A:A, ">=((41730*864000000000)+599266080000000000)")
=SUMIFS(A:A,A:A, "<=((41730*864000000000)+599266080000000000)")

Which seems to me to be incredibly weird; how does neither of these formulae catch any of the numbers in the formula's range? Surely the numbers HAVE to be greater, lesser or equal to the value I've given.

So my question is: what's wrong with these formulae!

Upvotes: 1

Views: 154

Answers (2)

user2140261
user2140261

Reputation: 8003

The way you are writting your formula you are doing a text compare of the numbers try the following: 

=SUMIFS(A:A,A:A, ">=" & (41730*864000000000)+599266080000000000)
=SUMIFS(A:A,A:A, "<=" & (41730*864000000000)+599266080000000000)

Upvotes: 0

Jerry
Jerry

Reputation: 71598

What's wrong is that you're not calculating the value of ((41730*864000000000)+599266080000000000) but merely putting it as text. If you want it to calculate, put it outside quotes:

=SUMIFS(A:A,A:A, ">="&((41730*864000000000)+599266080000000000))

Though I don't think you need SUMIFS for that. SUMIF should work:

=SUMIF(A:A, ">="&((41730*864000000000)+599266080000000000))

Upvotes: 2

Related Questions