I am trying to use SUNIFS without a helper column but do not get the expected result as shown in red. I expect these to be 350 but they are not, they are ignoring the last row of the data table, the value 47.
Any insight appreciated.
Mac Studio, macOS 15.2
Chalk it up to another bug in Numbers. I reported a different date-related SUMIFS bug to Apple nearly 3 years ago. When I reported it 3 years ago I suspected it had to do with the dates being in a format that was not one of the built-in ones for Canada (It was MM/DD/YY which is not one Numbers provides for Canada). This problem of yours expands the breadth of that bug. Your first formula does not work even if the region is US with US-format dates. It is strange that it works right if EOMONTH+DURATION is calculatd in a separate cell versus directly in the formula. I suspect it has something to do with the conditions being strings (due to the "<=" or ">="), not actual date values. It totally flops if you use a date&time format that is not one of the built-in ones for your region, like using MM/DD/YY dates when your region is DD/MM/YY. I have no idea what is going on in the code. Apple needs to fix it.
For the first formula, it works correctly for me if I use "<"&EOMONTH(A1,0)+1 as the condition. I recommend you do that anyway versus using DURATION.
The third formula is giving the correct result, it should not add the last value because it is out of the data range.
I recommend you report the bug to Apple using the menu item Numbers->Provide Numbers Feedback. Reference this thread.
Chalk it up to another bug in Numbers. I reported a different date-related SUMIFS bug to Apple nearly 3 years ago. When I reported it 3 years ago I suspected it had to do with the dates being in a format that was not one of the built-in ones for Canada (It was MM/DD/YY which is not one Numbers provides for Canada). This problem of yours expands the breadth of that bug. Your first formula does not work even if the region is US with US-format dates. It is strange that it works right if EOMONTH+DURATION is calculatd in a separate cell versus directly in the formula. I suspect it has something to do with the conditions being strings (due to the "<=" or ">="), not actual date values. It totally flops if you use a date&time format that is not one of the built-in ones for your region, like using MM/DD/YY dates when your region is DD/MM/YY. I have no idea what is going on in the code. Apple needs to fix it.
For the first formula, it works correctly for me if I use "<"&EOMONTH(A1,0)+1 as the condition. I recommend you do that anyway versus using DURATION.
The third formula is giving the correct result, it should not add the last value because it is out of the data range.
I recommend you report the bug to Apple using the menu item Numbers->Provide Numbers Feedback. Reference this thread.
Just a wild guess but this may have something to do with the fact that Numbers stores as date-time strings rather than as serial numbers as Excel does. That has advantages in many situations, but to do date comparisons in say, a SUMIF, I think it would first have to convert each date-time string, much the same way an "array" formula might manipulate each value individually in a range. Numbers doesn't support most array formulas. So to me it doesn't seem too surprising that it can't do this kind of operation reliably. The "helper" column approach has proven most reliable for me.
SG
Hi iwaddo,
Thank you for pushing this discussion forward from Converting start-of-day dates to end-of-d… - Apple Community
It is late here in Australia. Time for me to sleep and maybe wake up with a solution. Or, perhaps another user will step in to help.
Regards,
Ian.
Yes, I agree it feels like a bug.
I have implemented a workaround by adding a sequence column that serves as a straightforward method for comparing, adding, and summing various rows in relation to one another. I have disregarded the Date & Time column, except in cases where it is absolutely necessary.
Thank you for your help.
Hi, thank you. I appreciate your thoughts on this. A helper column certainly solves any issues and can make the formulas a little less complex to read. In much the same way, I've a helper table to drive my graphs and charts.
Thank you for your help.
be right back
Unexpected Numbers Result without Helper Column
