How can I sum current monthly values? Sum values in current month from dates (dd/mm/yyyy)? Is it possible?
There are a few ways. The attached spreadsheet has:
- SUBTOTAL the table needs to be filtered to current month
- SUMIF I needed to add a helper column (other people might be cleverer than me) but is automatic to current month as a helper cell uses TODAY()
-
Pivot table uses the helper column but I think you might be able avoid using it. It still needs the correct month filtered.
SumCurrentMonth.ods (22.4 KB)
1 Like
Sorry, I thought you wanted help, not to just spout off.
1 Like
It is me who is not clever and didn’t read the help files sufficiently.
Here is another method without helper cells, it uses SUMPRODUCT
SumCurrentMonth2.ods (20.5 KB)
cool down crumpy Gentleman!!
especially the Pivottable does a more intelligent Job as you expect.
Pivot_grouped_by_month_and_year.ods (20.8 KB)
2 Likes
Well, let me AGAIN put his way: every year, actually, every single year, months repeat. So here is an example: In in year 0, there was August. So there was in year 1. And so on. So, in 2021, there was August and surprise, in 2022 there is August. So CURRENT month isn’t just “August”. It’s “August in 2022”, not “August”. It’s like, let’s say “John”. There’s John Smith, John Silver. No, “August” isn’t a unique single universal value. So, CURRENT MONTH is August 2022, not “August”. Was I clear enough?
Tks! Here’s someone who can read. Nice job.
Here is SUMPRODUCT with years. Feel free to add any additional bits you like, the Calc Guide can be helpful, free download at English documentation | LibreOffice Documentation - LibreOffice User Guides.
SumCurrentMonth3.ods (19.0 KB)
BTW Calc is not designed for accounting but rather calculations in general.
The issue with SUMPRODUCT is that it doesn’t shortcut the calculation at the last line with data, so instead the whole column A:A or B:B a specific range A1:A99999 B1:B99999, should make it quicker.
Another solution it’s with:
=SUMIFS(B:B;A:A;">"&EOMONTH(NOW();-1);A:A;"<="&EOMONTH(NOW();0))