Sumifs and Offset combination

[erniepoe]

Hi all,

I am trying to get the value that is one column to the right of this formula, but am having problems finding the solution. I have tried Offset(Formula,0,1,1,1) but excel doesn't like it. Have simillarly tried putting the offset inside the Sumifs as well as inside the Index, but am not having any luck getting it to work.

Anybody know what I am doing wrong? Thanks so much.

=SUMIFS(INDEX('[November financials.xlsx]300 - STAT-A'!$B:$AG,0,MATCH("MTD",'[November financials.xlsx]300 - STAT-A'!$B$6:$AG$6,0)),'[November financials.xlsx]300 - STAT-A'!$A:$A,"*T&E*")

 

[Toadstool]

Hi Erniepoe,

The MATCH is giving the column number for the INDEX so if you want the column one to the right then add one. Here's a simplified version with it retrieving the Daily figure from column G but the MTD heading is in column F.

Erniepoe.xlsx
	A	B	C	D	E	F	G
4	Result	67
5
6		2020	2021	2022	YTD	MTD	Daily
7	ABC	1	2	3	4	5	6
8	XYZ	7	8	9	10	11	12
9	T&E	13	14	15	16	17	18
10	ZZZ	19	20	21	22	23	24
11	ABC	22	22	22	22	22	22
12	XYZ	38	39	40	41	42	43
13	T&E	44	45	46	47	48	49
14	ZZZ	50	51	52	53	54	55
Sheet1

Cell Formulas
Range	Formula
B4	B4	=SUMIFS(INDEX($B:$G,0,MATCH("MTD",$B$6:$G$6,0)+1),$A:$A,"*T&E*")

 

[erniepoe]

thank you Toadstool, this worked great! Cheers, and merry xmas...

 

[Toadstool]

thank you Toadstool, this worked great! Cheers, and merry xmas...

You're welcome & yuletide cheer to you also.