Need help dealing with dates in SUMIFS formulas (after updating LO from 6.3 to 7.6.2)

English
, ,
#1

So, for a few years I was successfully using this formula in the 6.3 version of LO Calc:

=SUMIFS(O5:O105;T5:T105;"*/03/2022";A5:A105;"C")

To sum all numbers in Column “O” when:
*Column “T” has cells with any day of March/2022. (The day is not important, just the month and year)
*Column “A” has cells with a “C” letter as text.

But after updating to 7.6.2, all my formulas (about a hundred of them, lol) were resulting to “0”. :neutral_face:
(The formula above is just an example, in my sheet I’m using lots of formulas similar to this for other conditions that also depend on date.)

It took me some time to figure out that the problem was the Date Format.
For column “T” I was using the “DD/MMM” display format for the dates (03/mar, 14/apr, 25/may, etc).
It was working fine in the old version, but now I had to convert to the DD/MM/YYYY format to fix all my formulas.

Any way I can better use months and years as conditions in SUMIFS formulas? (The day is not important to the formula, but I still need to insert the correct day in the cells).
My intention is to keep using the “DD/MMM” display format if possible (it’s visually important to me to quickly check it, I use this sheet all day).
Also, where I live day/month/year is the standard date format.

(PS: I know I can just add a new column to copy and visually display the DD/MMM format and keep the other hidden just for the formula sake, but I want to learn how to deal with this problem without this trick, if possible. :smiley: And sorry, I tried to search for answers by myself, but most topics about this are people using DATE functions, which didn’t help me…)

[erAck: edited to format code as code, see This is the guide - How to use the Ask site? - #6 by erAck]

#3

Please, can you attach a sample file to test?

#4

This site changes straight quotes to curly quotes unless it is inside the ` for preformatted text.

I copied your SUMIFS, corrected the curly quotes and it didn’t work but LO suggested a change which looked identical and it worked so maybe there is a zero width space or something in there. Make sure Enable Wildcards in formulae is ticked in Tools > Options > LibreOffice Calc > Calculate

SumifsWithDate.ods (18.1 KB)

1 Like
#6

https://ask.libreoffice.org/uploads/short-url/uMwhnNnlcKMK2vj9JcprE4i3NPb.ods

1 Like
#8

Please attach a sample file that worked in 6.3.

#10

@mariosv @mikekaganski
A new sample it’s not necessary because it’s basically like the one EarnestAI sent. He used my example formula and his SUMPRODUCT formula.

You’ll notice that if you change the Date Format to DD/MMM, his SUMPRODUCT formula keeps working and my SUMIFS formula breaks after recalculating (changes to zero).

So far, nobody gave me an option using SUMIFS while keeping a DD/MMM format, but it’s ok. I’m assuming it’s not possible.

#11

So you know what I need to see your issue. Bye then.

#12

In @Ernest sample, both function works for me. So without your sample, how can we know what is not working?
But let’s go, it’s you asking for a solution.

#13

So, did you change the date Column format to DD/MMM and the SUMIFS formula kept working? Do a Recalculate to double-check.
Or maybe it’s really just in my LibreOffice (maybe some configuration).

#15

If you change the formula criteria, to not have the first slash, it works in both formats, DD/MM/YYYY or MM/YYYY

=SUMIFS(O5:O105;T5:T105;"*03/2022";A5:A105;"C")

Maybe something to do with The COUNTIF and COUNTIFS functions mis-count on mixed numeric and text data and query by string

#19

Likely not, because that was a failure of 7.4.0 fixed in 7.4.7.2 and 7.5.3.2 and 7.6.0, whereas the OP said upgrading from 6.3(.whatever) to 7.6.2.

The "*/03/2022" depends on the format of the date cell values, so changing that will make things fail. With DD/MMM format you’d have to use "*/Mar" instead, but obviously that lacks the year so filtering for 2022 is not possible. The SUMPRODUCT() approach is the format independent solution here. Using SUMIFS() you can do similar with

=SUMIFS(O5:O105;YEAR(T5:T105);2022;MONTH(T5:T105);3;A5:A105;"C")

entered as array formula (close with Ctrl+Enter instead of just Enter), but there’s no benefit in it over using SUMPRODUCT(), au contraire, it might be slightly slower.

1 Like
#20

In defense of SUMIFS (not array formula): :slightly_smiling_face:

=SUMIFS(O5:O105; T5:T105; ">=" & DATE(2022;3;1);T5:T105; "<=" & DATE(2022;3;31);A5:A105;"C")
2 Likes
#21

SUMIFS being significantly faster (and less stressful) than SUMPRODUCT is a myth? I’ve read about it somewhere.

I’m using a lot of SUMIFS+SUMIFS non-array formulas for my multiple conditions… I’m trying to learn how to use OR (and maybe AND) functions that maybe I can use to reduce my formulas in the future. Also, some people suggested pivot tables, I may take a look at this approach too.

Anyway, I’m already converting some of my formulas to value for the old data (values that will not be changed anymore). I think it will help to reduce stress in the file. Although, I’m not sensing any “lag” or anything while using this sheet, for now.

Thanks for your suggestion.

#22

It depends… (read where?) on data and data layout (i.e. are there blank unused cells that don’t match the criteria anyway) and query. What SUMPRODUCT() does it creates interim arrays of all arguments and then just multiplies the positional values. With many blank or non-matching cells in a to-be-summed range like O5:O105 (but larger) creating an array is an unnecessary overhead. SUMIFS() uses query cell iterators for the conditions over ranges, creates a result array, and for fulfilled conditions asks cells in the to-be-summed range. With many fulfilled conditions that is slower, with less it is faster. On the other hand, creating an array of a cell range of contiguous numeric values (not formula cells) to be multiplied later by SUMPRODUCT() is faster if more cells match the condition than don’t. But those are implementation details. So yes, in general for large ranges SUMIFS() is probably faster and takes less memory, for small ranges it doesn’t matter, but formulating the conditions for SUMPRODUCT() may be more straight forward as all SUMIFS() conditions can only test for equality whereas the interim arrays of SUMPRODUCT() arguments may be results of any comparison.

1 Like
#2

You could use the SUMPRODUCT function in this way:

=SUMPRODUCT(O5:O105;YEAR(T5:T105)=2022;MONTH(T5:T105)=3;A5:A105=“C”)

What this function does is for each row, it takes the values of each argument then it multiplies them together. It then makes the sum of all these values for all the rows.

The trick here is when the year is 2022, the equality is 1, when the month is 3 the equality is 1, but if the year is 2023 for example, the result is 0 and then the multiplication of all arguments for that row is 0, which adds 0 to the sum.

Of course, you will need to change all your formulas…

1 Like
#5

Ok, your SUMPRODUCT formula is working with the DD/MMM date format (and @EarnestAl example sheet is working too).

Can I use a SUMIFS formula with these “YEAR” and “MONTH” as conditions? I tried, but it leads to error…

I don’t need them to “multiply” as you said… And I’ve read somewhere that SUMIFS calculates significantly faster than SUMPRODUCT, should I ignore that? (I’m talking about 200+ different SUMIFS formulas in a single sheet).

Should I avoid the SUMPRODUCT and just go with the “tricky” way I mentioned? (I mean add an extra hidden column with the date in other format, just for the formula sake).
Honestly, this would probably be faster than fixing all formulas, lol.

Thanks for the help already, guys!

#7

Sounds like you should use a pivottable as @villeroy has already provided

#9

It does even work with currency format or any other number format because a number format does not change any value.
97023.ods (16.1 KB) (same thing in all popluar spreadsheets of the past 3 decades)

1 Like
#14

Both @EarnestAl and @Steph1 SUMPRODUCT formulas are very valid solutions. Thank you, guys!

But changing all my sumifs formulas in the sheet would be too much work. I’m also not 100% sure if all my other formulas conditions would be viable with a sumproduct formula…

So I decided to just add an extra column that copies my DD/MMM cells (where I’ll look and insert the data everyday) to a DD/MM/YYYY format column (That I’ll keep hidden, just for the formulas’ usage).

By doing that, I ran into a problem: My blank date cells were copying to a “30/12/1899” date in the new column, instead of a blank cell. But I already solved that problem by using the function:
=IF(T5="";"";T5)
This way, the blank T cells returns to blank cells in the new column, as intended.

Thanks, everyone, for the replies.

#16

The pivot table solution as proposed by @Villeroy is the easiest and quickest solution and least likely to to require tweaking for exceptions.
SumifsWithDate&Pivot.ods (21.0 KB)

2 Likes

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.