Thread: [Solved] Index Match Function with a Date
View Single Post
 
Old 01-09-2023, 06:44 AM
ArviLaanemets ArviLaanemets is offline Windows 8 Office 2016
Expert
  
Join Date: May 2017
Posts: 873
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default
Quote:
Originally Posted by p45cal View Post
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)
Reply With Quote