SUMIFS and spanning a year

What is the exact nature of "I simply can't get it to work for 2014"?

My guess is that the dates and sum ranges don't extend out as far as they should. Do you dates go beyond the column AF? what about the sum values? If they do then you need to extend the ranges in your formula

Hi David,

I had the S1 and S2 cells formatted (for convenience) to dd-mmm without showing the year.

In Numbers, the Date And Time format is exactly that: a Date and a Time. Formatting a cell changes the display, but does not remove the components of the Date and Time. The components are always there, even if not displayed. Here is a screen shot where I entered dates into Column A and formatted that column in Inspector (*not* by custom format) to show day-month-year.

Then in Column B, (formula =A2 and filled down) the Cell Format in Inspector is day-month (the year is not displayed).

In Column C, (formula =B2 and filled down) the Cell Format in Inspector is day-month-year. The year is revealed once again, because it was not lost.

Are you using a Custom Format?

Regards,

Ian.

I can confirm a problem with cell formatting of dates messing up SUMIFS results. It should not have an effect but it definitely does. My testing only used the built-in formats for Date & Time.

Here is one set of tests. The dates in column A and D are exactly the same for the two tests; cell formatting is the only difference. The results (cells D4 and D9) are not the same.

Hi David and Badunit,

If SUMIFS is not working correctly with dates, perhaps another approach is needed.

using SUMIFS() to test between two dates and sum items in that range.

What condition are you testing? Please explain your overall aim.

I am thinking that maybe pulling out the components of the date (Month, Day, Year) as number values, then working with them could be a solution.

Regards,

Ian.

Hello

I think it is because SUMIFS() accepts condition as string such as ">="&A1, where & is string concatenation operator. The string on the right-hand side seems to be interpreted by using general rule of string-to-value conversion.

E.g.

Given A1 = 2014-01-01 formated as Jan-01, ">="&A1 yields ">=Jan-01" which is interpreted as ">="&DATE(2013,1,1) and not ">="&DATE(2014,1,1).

Given A1 = 2013-01-01 formated as Jan-13, ">="&A1 yields ">=Jan-13" which is interpreted as ">="&DATE(2013,1,13) and not ">="&DATE(2013,1,1).

Apart from using appropriate date-time format in cell referenced in condition in SUMIFS() etc., you may also build full date/time string by using such construct as ">="&(A1-NOW()+NOW()) or ">="&(A1-TODAY()+TODAY()), which should work regardless of the format of A1.

This is very confusing but I'm not sure it is classified as bug. As far as the behaviour of & operator is concerned, it is as expected.

If only there're two separate parameters for comparator and value in the condition part of those functions...

Regards,

H

I believe you are correct that the conversion to a string is the problem but it should be using the underlying value in the cell, not on the formatted version being displayed. I repeated the test with a custom format that tacked "AA" onto the end of the value and the formula ignored that bit. So, in this case, it is using the actual value. I'm not sure what is going on but if cell formatting affects the results (like in the previous test, not this one), it is a bug.

Hello Badunit,

I'm not sure I understand your example correctly, but it appears D6 and D7 show strings with 3 components of date which can be interpreted as dates without ambiguity, in which case SUMIFS() in D8 returns the result we expect.

The question is the format of dates in D6 and D7 and not the format of dates in A. SUMIFS() accepts values in A as independent parameter and so it can honour the original type of values in A, while it accepts values in D6 and D7 as part of string which is interperted as condition and thus it cannot know the original type of values in D6 and D7 but only re-interpret their current string values which are affected by their current format.

This issue is not limited to date/time values. Number format of cells used in condition part of those functions also affects the result.

E.g.

Case 1 as we may expect:

A2  1.001
B2  1
C2  =A2                                 # 1.001 (formated as 3 decimal places)
D2  =SUMIFS(B2:B2,A2:A2,"<="&C2)        # 1

Case 2 as we may not expect:

A4  1.001
B4  1
C4  =A4                                 # 1.00 (formated as 2 decimal place)
D4  =SUMIFS(B4:B4,A4:A4,"<="&C4)        # 0

Case 3 as a workaround for Case 2:

A6  1.001
B6  1
C6  =A6                                 # 1.00 (formated as 2 decimal places)
D6  =SUMIFS(B6:B6,A6:A6,"<="&(C6+0))    # 1

Formula in D6 in Case 3 yields the desired result 1 regardless of the format of C6. This is analogous to the previous workaround for date using, e.g., ">="&(A1-NOW()+NOW()) in lieu of ">="&A1 where A1 contains date with arbitrary format.

I'm yet to convince myself to consider it bug because it is the expected behaviour of & operator which honours the format:

A8  1.001
C8  =A8             # 1.00 (formated as 2 decimal places)
D8  =""&C8          # 1.00

However, I do agree that it is defect of condition parameter of those functions such as SUMIF(), SUMIFS(), COUNTIF() and COUNTIFS().

All the best,

H

Hiroto wrote:

Hello Badunit,

I'm not sure I understand your example correctly, but it appears D6 and D7 show strings with 3 components of date which can be interpreted as dates without ambiguity, in which case SUMIFS() in D8 returns the result we expect.

The question is the format of dates in D6 and D7 and not the format of dates in A.

You are correct. It does give a different result when I apply the custom format to the dates in column D instead of in column A.

I guess I will backpedal on calling this a bug. Formulas will in some instances use the value displayed in the cell and in other instances use the actual cell value. It is inconsistent.

Examples:

A1 has the number 6 in it but has a custom format that makes it display as 6AA

=A1 will result in 6

=A1&"" will result in 6AA

A1 has the date Jan 1, 2013 in it but has a custom format that makes it display as Jan 1, 2013AA

=A1 will result in Jan 1, 2013AA and it works correctly in date-related formulas as if there was no "AA"

=A1&"" will result in Jan 1, 2013AA but as text not a date

So, depending on the formula and depending on the type of data, it might or might not use the cell format in downstream formulas.

Yes, I can confirm that custom format applied to number in A1 is stripped by the formula =A1. Except for it, the formula =A1 seems to inherit the format of A1. This is not consistent with the behaviour on date where even custom format is inherited by the formula =A1.

Honestly I always feel that complex format features of Numbers are very confusing. In my opinion, spreadsheet should display the whole data as stored in cells, and if we are to edit the format of data, we should do it explicitly by formula. This way we can distinguish data from formats and guarantee the uniqueness of data.

Kind regard,

H