Update to sum from year and month

[itsgrady]

I have two formulas that are summing according to month and not the month and the year. Since the new year started, they are no longer providing the correct information. They are adding all cells with January and I need only the 2024 year to sum. I have added a cell (AD3) for the year.

1st Formula:

=IF(SUMPRODUCT((--(MONTH(Table2[Date])=M7)),Table2[T1 Day])=0,"",SUMPRODUCT((--(MONTH(Table2[Date])=M7)),Table2[T1 Day]))

M7 has the number “1” for January, M8 has the number “2” for February, etc.

Table2[T1 Day] is what the column that is summing.

I have added a cell “AD3” for the year in AD3

2nd Formula:

This formula also need updating to sum by the year and the month.

=IFERROR(SUMPRODUCT((--(MONTH(Table2[Date])=$M7)),Table2[Acid])/O7,"")

Thanks for your help.

 

[felixstraube]

How about

=LET(
s,SUMPRODUCT((MONTH(Table2[Date])=M7)*(YEAR(Table2[Date])=$AD$3)*Table2[T1 Day]),
IF(s=0,"",s)
)

and for the second one:

=IFERROR(SUMPRODUCT((MONTH(Table2[Date])=V7)*(YEAR(Table2[Date])=$AD$3)*Table2[Acid])/O7,"")

 

[itsgrady]

How about

=LET(
s,SUMPRODUCT((MONTH(Table2[Date])=M7)*(YEAR(Table2[Date])=$AD$3)*Table2[T1 Day]),
IF(s=0,"",s)
)

and for the second one:

=IFERROR(SUMPRODUCT((MONTH(Table2[Date])=V7)*(YEAR(Table2[Date])=$AD$3)*Table2[Acid])/O7,"")

Thanks for taking time to help. I appreciate the updated codes. They worked perfectly and now I am ready for 2024, 2025, and beyond.

 

[felixstraube]

Happy to help. Thanks for the feedback!