this formula works
=SUMPRODUCT((MONTH($readings.$A$9:$A$2000)=1)*(YEAR($readings.$A$9:$A$2000)=2026),$readings.$E$9:$E$2000)
but if I substitute column F instead of A in the criteria it gives the error #VALUE!
Attach an example file to your question.
<edit 2026-07-25 about 12:50 UTC>
@PawluTaMalta
Didn’t you understand?
If you don’t want to take the advice given, you should explain why.
So far, this thread has turned into a maze that’s nothing but a waste of time for everyone involved.
with my configuration 26.2 4.2 W11
=SUMPRODUCT((MONTH($readings.$A$9:$A$2000)=1)*(YEAR($readings.$A$9:$A$2000)=2026),$readings.$E$9:$E$2000)
give Err508
Thanks to your detailed example and selfless help I have found the solution to my problem.
I included the Start date and End date for each month in the Charts sheet and used this formula
=SUMPRODUCT(ISNUMBER($readings.$F$8:$F$2000),$readings.$F$8:$F$2000>=$Y$147,$readings.$F$8:$F$2000<=$Y$148,$readings.$E$8:$E$2000)
To get the desired result for the monthly Settled figure.
I am attaching a copy of the completed sheet; perhaps it will benefit a future user.
Much appreciated
Paul Buhagiar
libre forum copy PV HDD Panels average daily units produced.ods (3.1 MB)
We could have identified the text in column F that caused the “#VALUE” error using a file as early as 4 days ago.
PKG_libre forum copy PV HDD Panels average daily units produced.ods (3,0 MB)
I am sorry I have unwittingly been unhelpful to solve my own problem.
This is the first time I was using the forum.
My thanks go to all the selfless people who try to help us novices.
Have a good day
Paul Buhagiar
hello
your formula is
=SUMPRODUCT((MONTH($readings.$A$9:$A$2000)=1)*(YEAR($readings.$A$9:$A$2000)=2026),$readings.$E$9:$E$2000)
There is “,” in front of the last calculation
replace “,” by “*”
=SUMPRODUCT((MONTH($readings.$A$9:$A$2000)=1)*(YEAR($readings.$A$9:$A$2000)=2026)*($readings.$F$9:$F$2000))
hello
idem for replace “,” by “*”
=SUMPRODUCT((MONTH($readings.$A$9:$A$2000)=1)*(YEAR($readings.$F$9:$F$2000)=2026)*($readings.$E$9:$E$2000))
Hello,
maybe also use MONTH from F instead of mixing A and F ?
… to calculate the aggregated duration since 1899-12-30 obviously 
hello
sorry error
the formula is
=SUMPRODUCT((MONTH($readings.$F$9:$F$2000)=1)*(YEAR($readings.$F$9:$F$2000)=2026)*($readings.$E$9:$E$2000))
In my opinion, the error comes from the “,” instead of the “*” in the initial formula.
=SUMPRODUCT((MONTH($readings.$A$9:$A$2000)=1)*(YEAR($readings.$A$9:$A$2000)=2026),$readings.$E$9:$E$2000)
PivotData.ods (82.7 KB)
How to aggregate data by months, years and/or any other category with pivot tables. The sample does not contain a single formula.
Not having a clarifying example and satement by the original questioner I did 2 steps:
disask_136608_related_teaching.ods (61.6 KB)
Also works with
1746=SUMIFS($E$25:$E$2024;$A$25:$A$2024;">="&$A$16;$A$25:$A$2024;"<="&$B$16)
And if data are in columns beginning from row 1, column address can be used, SUMIFS not like SUMPRODUCT cuts at the last row with data, like
=SUMIFS($E:$E;$A:$A;">="&$F$16;$A:$A;"<="&$G$16)