SUMIFS using "greater than or equal to" with text values

Using the following simplified example, I want to calculate the sum where Category="Graduation" and Date >= 1986-05-01.

Category Date Count
Graduation 1985-06-15 1
Graduation 1985-09-12 2
Graduation 1986-05-21 3
Graduation 1986-06-06 4
Graduation 1986-07-03 7
Transfer 1986-08-14 3
Graduation 1986-08-20 1

The dates are all text, i.e. NOT Excel dates. Being in yyyy-mm-dd format, the dates follow a proper ordering.

Here are several formula I have used and their result, leading to my real question at the end:

=SUMIFS(C2:C8,A2:A8,"Graduation",B2:B8,"='1986-05-21'") 0 Incorrect
=SUMIFS(C2:C8,A2:A8,"Graduation",B2:B8,"=1986-05-21") 3 Correct
=SUMIFS(C2:C8,A2:A8,"Graduation",B2:B8,"1986-05-21") 3 Correct

So for equality you don't put quotes around the value. That's fine.

=SUMIFS(C2:C8,A2:A8,"Graduation",B2:B8,">1986-05-21") 0 Incorrect
=SUMIFS(C2:C8,A2:A8,"Graduation",B2:B8,">'1986-05-21'") 12 Correct

So for greater than, you do need to put quotes around the value.

=SUMIFS(C2:C8,A2:A8,"Graduation",B2:B8,">='1986-05-21'") 12 Incorrect: should be 15

Seems like >= is treated as though it was >.

=SUMIFS(C2:C8,A2:A8,"Graduation",B2:B8,"<'1986-05-21'") 6 Incorrect: should be 3
=SUMIFS(C2:C8,A2:A8,"Graduation",B2:B8,"<='1986-05-21'") 6 Correct

Seems like < is treated as though it was <= (i.e. the reverse of the >= case).

So, can you help me to adjust the syntax so that SUMIFS gets greater than and less than correct with text values?

1 Answer

The COUNTIF(S)/SUMIF(S) family of functions generally attempt to interpret your data as numeric where possible, though here there is evidently some confusion as it does not appear that it can consistenly interpret both your range values and your criterion as being of the same format.

In this case:

=SUMIFS(C2:C8,A2:A8,"Graduation",B2:B8,">1986-05-21")

Excel recognises your entries in B2:B8, although formatted as text, as potential date (i.e. numerical) values.

However, at the same time, it (unhelpfully) interprets your criterion - "1986-05-21" - as a text value, and so, since Excel does not consider any numerical (which is precisely what dates are in Excel - 21/05/1986 is 31553) to be "greater" than any text value (enter e.g. =1000000>"1" in a cell somewhere - the answer is FALSE), the answer is zero.

When you add apostrophes:

=SUMIFS(C2:C8,A2:A8,"Graduation",B2:B8,">'1986-05-21'")

Excel interprets both your criterion and entries in B2:B8 as text, and so does a straight text-to-text comparison.

The reason that you obtain 12, and not 15, for:

=SUMIFS(C2:C8,A2:A8,"Graduation",B2:B8,">='1986-05-21'")

is that the entry in B4 is 1986-05-21 (without an apostrophe: one may appear in the formula bar, though this is not technically part of the string, simply Excel's way of indicating that the cell value is text) and the criterion is '1986-05-21' (with "genuine" apostrophes). And you can easily test in a cell somewhere that:

="1986-05-21">="'1986-05-21'"

is considered by Excel as FALSE (I presume Excel interprets the leading apostrophe in the latter as meaning it is "greater" than the former - I do not know the ins and outs of these interpretations: strangely, ="/a">"a" returns FALSE whereas ="'a">"a" returns TRUE).

All this is perhaps a good reason to switch to SUMPRODUCT, which does not seem to suffer these peculiar ambiguities:

=SUMPRODUCT((A2:A8="Graduation")*(B2:B8>="1986-05-21")*C2:C8)

Regards

2

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy