Index Match Function with a Date

ArviLaanemets · #7 01-09-2023, 04:34 AM

Much easier and more flexible way

Code:

=SUMIFS(Data!$D$2:$D$13,Data!$B2:$B13,Calculation!$B$1,Data!$A$2:$A$13,Calculation!$B$3)

Define fields Calculation!$B$1 and Calculation!$B$5 as Names, like nPFNo and nDate - the same formula will be

Code:

=SUMIFS(Data!$D$2:$D$13,Data!$B2:$B13,nPFNo,Data!$A$2:$A$13,nDate)

Define the Table on sheet Data as Defined Table, eg. tData, and the same formula will be

Code:

=SUMIFS(tData[Salary],tData[[PF No]],nPFNo,tData[Month],nDate)

Replace column header [PF No] in tData Table with PF, and check out the formula in Table syntax for Salary in Calculation Sheet - you see the formula was changed properly too!

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Index and Match Function	mismag	Excel	5	05-29-2019 11:26 AM
Excel - Index and Match Function ,First Second and Third Match	paulzy95	Excel	10	09-29-2016 10:46 PM
Index Match Function across different worksheets	shay_mt	Excel	2	04-27-2015 06:04 AM
Index Match function	jackzha	Excel	5	12-03-2014 12:43 PM
Moving a Index/Match function	FraserKitchell	Excel	2	01-19-2010 09:38 AM