I’m using this sumif but rather than summing up its just giving the value in the top cell. Putting exactly the same formula in Excel sums up properly. What am I doing wrong?
=SUMIF(B:B, “Jan 22”, AY:AY)
So, I have a row for each day of the year with the day dates in col. B formatted as Jan 22, Feb 22, etc. I’m trying to add up each months figures from col. AY.
If I can get this to work then I’ll do another for Feb 22 etc.
I really want to use Libre rather than Excel.
if you insist on such strange format-dependent formulas…stay with Excel!
If not, try instead: →→Data→→Pivot table.
enter with ctrl+shift+enter
=SUMIF(TEXT(B:B,"MMM YY"), "Jan 22", AY:AY)
pivottable_example.ods (15.6 KB)
You can use SUMIFS.
=SUMIFS(AY:AY; B:B; ">=" & DATE(2022;1;1); B:B; "<=" & DATE(2022;1;31))
By the way, in Excel your formula will only work on computers with regional settings that provide English language.
Given how @alanhig99’s formula is worded, the Of course, I’m wrong, because I somehow missed that you checked the whole month, not the whole day (excuse my poor attention >=/<= precautions are excessive: original formula would only work for dates without time parts, so a simple = is enough.
)
Hey, that seems to work. Gives me the January total. Would you tell me how to alter it to Feb etc please. Sorry, I’m a user, but absolutely not an expert. (I’m in the UK so I’m set to UK dates etc).
Ahh, I worked that out. But I need to change the month number and also the 31 to 28 days for Feb etc. But that’s OK. So thank you very much.
Thanks for the reply. I couldn’t get your SUMIF to work. I’m sure your pivot table is a good answer but I’ve never done a pivot table so too nervous. But I’ve now got a SUMIFS answer which works so gonna use that.
Thanks again.
The DATE function has the following parameters: year, month, day. For February we can write like this:
=SUMIFS(AY:AY; B:B; ">=" & DATE(2022;2;1); B:B; "<" & DATE(2022;3;1))
thus not to deal with leap years.
Now that’s brilliant. Took me a moment to get it , but you’re going from the 1st of each month to < the 1st of the next. Love it. Thank you very much.
Don’t be afraid! Pivot tables are not rocket science, try and play with them, the source data remain unchanged, you can’t break anything!
It works in Calc, iif you make sure to use a locale that can interpret strings like “Jan 22” as dates.
If any spreadsheet software interprets "Jan 22" as a date today, that will be 2022-01-22, and the same software must interpret the same string in the same sheet as a different date (2023-01-22) next year. Who should accept such a nonsense? "Jan 22" either is accepted as an arbitrary string like “§§71ff” or can’t be used in any IT application at all. It definitely isn’t a date. Where am I wrong?
no it doesnt because it interpretes the literal "Jan 22" exactly as date: 2022-01-01 , but the OP Intention was to match the whole Month
Only in one thing: expecting humans to do rational things 
I agree that OP’s formula is bad, locale-specific, unportable (and - likely not the way how OP expected it - because, as I tested with Excel, it also doesn’t sum whole months that way) … yet, it works in Calc just as in Excel, if you set up Calc according to your needs
Hmm, interesting. Let me check.
No, Excel also doesn’t sum a whole month, regardless how the cells are formatted.
You’re right, it doesn’t actually work in Excel. This was a csv file that I imported to excel and I see now that it put all dates to the 1st of the month. That way excel is adding up the whole month, but now I’ve put them back to day dates it doesn’t work.
Everything “works” in one or another way under any circimstances. If the way it works can -even stressing the terms to the ultimate- be accepted as reasonable is a different question.
Lupp sometimes is quoted with “If the world turns to MS thinking, it cannot survive for long.”
@karolus: Where (locale) would Jan 22 actually be interpreted as “january of the year 2022” outside the mind of a specific person? Even Excel shouldn’t accept that. If, on the other hand, the SUMIF() condition is meant to compare to a string, B:B needs to contain strings.
BTW Jan 22 might be the way a German abberviates a specific month (badly). A German, however, wouldn’t use commas in a spreadsheet as parameter delimiters.
===Editing===
The fact of having imported data from a csv should have been mentioned in the first line of the question. We contributors should have had the chance in mind anyway. The world is as it is, after all.
I can see that you’re right, that’s clearly why I couldn’t get it to work. Silly of me. The SUMIFS solution that I’ve been given does work (but I must set the number of days appropriately for each month).
Using EOMONTH() you don’t need to know the days of the month.
=SUMIFS(B:B; B:B; ">=" & DATE(2022;1;1); B:B; "<=" &EOMONTH(DATE(2022;1;1);0))