|
02-03-2023, 10:39 PM
|
|
Novice
|
|
Join Date: Dec 2022
Posts: 9
|
|
Quote:
Originally Posted by ArviLaanemets
All OP's month values in Data sheet were 1st of month, so it was logical to assume this is a part of routine. In case it is not the case, OP can have a calculated column which returns the 1st of month based on date in column Month in SUMIFS formula. Or OP can redesign the table, and use instead date values for months integer values in format yyyymm.
OP can also take some 15 years back (Into time when there wasn't any SUMIFS/COUNTIFS functions in Excel, and SUMPRODUCT was used instead - with a bonus that you could refer to calculations instead columns)
Code:
=SUMPRODUCT(--(YEAR(Data!$A$2:$A$13)=YEAR(Calculation!$B$3)),--(MONTH(Data!$A$2:$A$13)=MONTH(Calculation!$B$3)),--(Data!$B$2:$B$13=Calculation!$B$1),Data!$D$2:$D$13)
|
Thanks for all of you
|