Quote:
Originally Posted by p45cal
I took it that datewise, the OP wanted only a month and year match, so that dates didn't need to be the first of the month everywhere.
|
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)
01-09-2023, 06:44 AM
Similar Threads
Index and Match Function
Windows 8 
Office 2016
Threaded Mode