Sumifs with Dynamic Columns

[RudRud]

Hi Sir,

I'd like to ask for help to make the sum range and criteria columns dynamic, bcos the values2 and name columns are changing every time therefore I need to change the formula whenever the said columns have been changed

C:C
A:A

Book1
	A	B	C	D	E	F	G	H
1	Name	Value	Value2					Value2
2	Rud	1	8				Rud	16
3	Test	2	8				Test	8
4	Rud	3	8
Sheet1

Cell Formulas
Range	Formula
H2:H3	H2	=SUMIFS(C:C,A:A,G2)

Thanks

 

[Peter_SSs]

Something like this?

23 03 03.xlsm
	A	B	C	D	E	F	G	H
1	Name	Value	Value2					Value2
2	Rud	1	8				Rud	16
3	Test	2	8				Test	8
4	Rud	3	8
5
RudRud

Cell Formulas
Range	Formula
H2:H3	H2	=SUMIFS(INDEX(B:E,0,MATCH(H$1,B$1:E$1,0)),A:A,G2)

 

[RudRud]

Well Noted, Thanks for your assist Sir

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

 

[RudRud]

Hi Sir, Sorry for asking again

Is that possible for the sum range and criteria range based on other selected Workbook Name and Sheets?

For Example, I want to sumifs the Book3 Sheet1 area (always different based on what I input in the k2 and k3 values) instead of the current workbook

Book2
	A	B	C	D	E	F	G	H	I	J	K
1	Name	Value	Value2					Value2
2	Rud	1	8				Rud	16		Wb Name:	[Book3]
3	Test	2	8				Test	8		Wb sheets:	Sheet1
4	Rud	3	8
5
Sheet1

Cell Formulas
Range	Formula
H2:H3	H2	=SUMIFS(INDEX(B:E,0,MATCH(H$1,B$1:E$1,0)),A:A,G2)

Book3
	A	B
1	Name	Value2
2	Rud	5
3	Rud	5
Sheet1

 

[RudRud]

Up

 

[Peter_SSs]

I'm afraid that I don't have anything for you. Perhaps somebody else will step in with an offering though.

 

[fjns]

Hi, see the linked files for a possible solution for your #5 message...

The formula used in the table:
=SUMIFS(INDEX(INDIRECT(K$2&K$3&"!B:E"),0,MATCH(H$1,INDIRECT(K$2&K$3&"!B$1:E$1"),0)),INDIRECT(K$2&K$3&"!A:A"),G2)

Book2.xlsx
Book3.xlsx

 

[RudRud]

Hi, see the linked files for a possible solution for your #5 message...

The formula used in the table:
=SUMIFS(INDEX(INDIRECT(K$2&K$3&"!B:E"),0,MATCH(H$1,INDIRECT(K$2&K$3&"!B$1:E$1"),0)),INDIRECT(K$2&K$3&"!A:A"),G2)

Book2.xlsx
Book3.xlsx

Hi, can now, Thanks so much for your kind assist sir

 

[fjns]

Hi, I am very glad that the formula works.
If I have enough knowledge, I am happy to help at any time.