Sum column according to header and matched value

[thanksamillion101]

Hello and thank you ahead of time! I am needing to sum columns according to specified header names, reason being that I cannot specify a column range because the amount of columns may change when I run a report, but header names will remain the same. I need a formula to sum the amount of Units of Product "I" sold in TX with an Id of 375 between (Id 1) 350 and (Id 2) 400. The answer is 4.0 I have tried several formulas and now I am not sure which formula to use, sumif and index and match. Please help!

INDEX(A2:G7,MATCH("I",INDEX(A2:G7,MATCH("Product",A1:G1,0),),0))

	A	B	C	D	E	F	G
1	Date	Id 1	Id 2	Name	Product	Units	State
2	1/1/2022	100	200	Sam	AG	2.5	KS
3	3/4/2021	600	700	Tim	EZ	1.3	OK
4	5/6/2021	350	400	John	I	4	TX
5	1/7/2021	500	700	Sam	AG	7.5	KS
6	3/4/2022	250	400	Tim	EZ	6	OK
7	5/1/2022	600	705	John	I	8	TX

 

[KRice]

I don't understand what you mean by "between (Id1) 350 and (Id2) 400". Do you mean Id1=350 and Id2=400?

MrExcel_20220803.xlsx
	A	B	C	D	E	F	G	H	I	J
1									ID lower	350
2	Date	Id 1	Id 2	Name	Product	Units	State		ID upper	400
3	1/1/2022	100	200	Sam	AG	2.5	KS		Product	I
4	3/4/2021	600	700	Tim	EZ	1.3	OK		Sum	4
5	5/6/2021	350	400	John	I	4	TX
6	1/7/2021	500	700	Sam	AG	7.5	KS
7	3/4/2022	250	400	Tim	EZ	6	OK
8	5/1/2022	600	705	John	I	8	TX
Sheet2

Cell Formulas
Range	Formula
J4	J4	=SUMIFS($F$3:$F$8,$B$3:$B$8,$J$1,$C$3:$C$8,$J$2,$E$3:$E$8,$J$3)

 

[thanksamillion101]

I don't understand what you mean by "between (Id1) 350 and (Id2) 400". Do you mean Id1=350 and Id2=400?

MrExcel_20220803.xlsx
	A	B	C	D	E	F	G	H	I	J
1									ID lower	350
2	Date	Id 1	Id 2	Name	Product	Units	State		ID upper	400
3	1/1/2022	100	200	Sam	AG	2.5	KS		Product	I
4	3/4/2021	600	700	Tim	EZ	1.3	OK		Sum	4
5	5/6/2021	350	400	John	I	4	TX
6	1/7/2021	500	700	Sam	AG	7.5	KS
7	3/4/2022	250	400	Tim	EZ	6	OK
8	5/1/2022	600	705	John	I	8	TX
Sheet2

Cell Formulas
Range	Formula
J4	J4	=SUMIFS($F$3:$F$8,$B$3:$B$8,$J$1,$C$3:$C$8,$J$2,$E$3:$E$8,$J$3)

I don't understand what you mean by "between (Id1) 350 and (Id2) 400". Do you mean Id1=350 and Id2=

The sum amount is for the amount of Units of Product "I" sold in TX by and employee whose Id is "375", so the formula would specify needing to match the range falling in between (Id 1) 350 and (Id 2) 400. Hope that is a better explanation

 

[thanksamillion101]

I don't understand what you mean by "between (Id1) 350 and (Id2) 400". Do you mean Id1=350 and Id2=400?

MrExcel_20220803.xlsx
	A	B	C	D	E	F	G	H	I	J
1									ID lower	350
2	Date	Id 1	Id 2	Name	Product	Units	State		ID upper	400
3	1/1/2022	100	200	Sam	AG	2.5	KS		Product	I
4	3/4/2021	600	700	Tim	EZ	1.3	OK		Sum	4
5	5/6/2021	350	400	John	I	4	TX
6	1/7/2021	500	700	Sam	AG	7.5	KS
7	3/4/2022	250	400	Tim	EZ	6	OK
8	5/1/2022	600	705	John	I	8	TX
Sheet2

Cell Formulas
Range	Formula
J4	J4	=SUMIFS($F$3:$F$8,$B$3:$B$8,$J$1,$C$3:$C$8,$J$2,$E$3:$E$8,$J$3)

I cannot use a column range and needing to match column headers because the column locations will change when I run a report, but header names will always remain the same.

 

[KRice]

Sorry...I missed the point about not knowing a specific range. Here is an idea that requires you to specify a large range where the columns will be found...and then using INDEX/MATCH to return each column of interest, wherever they might be in that range. But I still don't understand the Id1 and Id2 point. The formula below requires Id1=350 and Id2=400. What does Id1 and Id2 mean? Are those the upper and lower limits for Id's to be considered, so that someone with an Id of 375 would be included? Where are the Id numbers associated with each "Name" found?

MrExcel_20220803.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	Date	Id 1	Id 2	Name	Product	Units	State				ID lower	350
2	1/1/2022	100	200	Sam	AG	2.5	KS				ID upper	400
3	3/4/2021	600	700	Tim	EZ	1.3	OK				Product	I
4	5/6/2021	350	400	John	I	4	TX				State	TX
5	1/7/2021	500	700	Sam	AG	7.5	KS				Sum	4
6	3/4/2022	250	400	Tim	EZ	6	OK
7	5/1/2022	600	705	John	I	8	TX
Sheet2

Cell Formulas
Range	Formula
L5	L5	=SUMPRODUCT(INDEX($A$2:$I$99,,MATCH("Units",$A$1:$I$1,0)),(INDEX($A$2:$I$99,,MATCH("Product",$A$1:$I$1,0))=$L$3)*(INDEX($A$2:$I$99,,MATCH("State",$A$1:$I$1,0))=$L$4)*(INDEX($A$2:$I$99,,MATCH("Id 1",$A$1:$I$1,0))=$L$1)*(INDEX($A$2:$I$99,,MATCH("Id 2",$A$1:$I$1,0))=$L$2) )

 

[thanksamillion101]

Sorry...I missed the point about not knowing a specific range. Here is an idea that requires you to specify a large range where the columns will be found...and then using INDEX/MATCH to return each column of interest, wherever they might be in that range. But I still don't understand the Id1 and Id2 point. The formula below requires Id1=350 and Id2=400. What does Id1 and Id2 mean? Could some row have an Id1 of 375?...if so, should it be considered?

MrExcel_20220803.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	Date	Id 1	Id 2	Name	Product	Units	State				ID lower	350
2	1/1/2022	100	200	Sam	AG	2.5	KS				ID upper	400
3	3/4/2021	600	700	Tim	EZ	1.3	OK				Product	I
4	5/6/2021	350	400	John	I	4	TX				State	TX
5	1/7/2021	500	700	Sam	AG	7.5	KS				Sum	4
6	3/4/2022	250	400	Tim	EZ	6	OK
7	5/1/2022	600	705	John	I	8	TX
Sheet2

Cell Formulas
Range	Formula
L5	L5	=SUMPRODUCT(INDEX($A$2:$I$99,,MATCH("Units",$A$1:$I$1,0)),(INDEX($A$2:$I$99,,MATCH("Product",$A$1:$I$1,0))=$L$3)*(INDEX($A$2:$I$99,,MATCH("State",$A$1:$I$1,0))=$L$4)*(INDEX($A$2:$I$99,,MATCH("Id 1",$A$1:$I$1,0))=$L$1)*(INDEX($A$2:$I$99,,MATCH("Id 2",$A$1:$I$1,0))=$L$2) )

Id1 is a start of a batch and id 2 is the end of a batch, so if I need to find a specified id of 375, the formula would specify Id <400 and > 350.

 

[KRice]

Try this...

MrExcel_20220803.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	Date	Id 1	Id 2	Name	Product	Units	State				Filters
2	1/1/2022	100	200	Sam	AG	2.5	KS				ID	375
3	3/4/2021	600	700	Tim	EZ	1.3	OK				Product	I
4	5/6/2021	350	400	John	I	4	TX				State	TX
5	1/7/2021	500	700	Sam	AG	7.5	KS				Sum	4
6	3/4/2022	250	400	Tim	EZ	6	OK
7	5/1/2022	600	705	John	I	8	TX
Sheet2

Cell Formulas
Range	Formula
L5	L5	=SUMPRODUCT(INDEX($A$2:$I$99,,MATCH("Units",$A$1:$I$1,0)),(INDEX($A$2:$I$99,,MATCH("Product",$A$1:$I$1,0))=$L$3)*(INDEX($A$2:$I$99,,MATCH("State",$A$1:$I$1,0))=$L$4)*(INDEX($A$2:$I$99,,MATCH("Id 1",$A$1:$I$1,0))<=$L$2)*(INDEX($A$2:$I$99,,MATCH("Id 2",$A$1:$I$1,0))>=$L$2) )

 

[thanksamillion101]

Try this...

MrExcel_20220803.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	Date	Id 1	Id 2	Name	Product	Units	State				Filters
2	1/1/2022	100	200	Sam	AG	2.5	KS				ID	375
3	3/4/2021	600	700	Tim	EZ	1.3	OK				Product	I
4	5/6/2021	350	400	John	I	4	TX				State	TX
5	1/7/2021	500	700	Sam	AG	7.5	KS				Sum	4
6	3/4/2022	250	400	Tim	EZ	6	OK
7	5/1/2022	600	705	John	I	8	TX
Sheet2

Cell Formulas
Range	Formula
L5	L5	=SUMPRODUCT(INDEX($A$2:$I$99,,MATCH("Units",$A$1:$I$1,0)),(INDEX($A$2:$I$99,,MATCH("Product",$A$1:$I$1,0))=$L$3)*(INDEX($A$2:$I$99,,MATCH("State",$A$1:$I$1,0))=$L$4)*(INDEX($A$2:$I$99,,MATCH("Id 1",$A$1:$I$1,0))<=$L$2)*(INDEX($A$2:$I$99,,MATCH("Id 2",$A$1:$I$1,0))>=$L$2) )

Worked perfectly, no errors!! Thanksamillion!!! I apologize for the confusion and thank you for your time and patience, greatly appreciated!!

 

[KRice]

That's great!...I'm happy to help.

 

[thanksamillion101]

Sorry...I missed the point about not knowing a specific range. Here is an idea that requires you to specify a large range where the columns will be found...and then using INDEX/MATCH to return each column of interest, wherever they might be in that range. But I still don't understand the Id1 and Id2 point. The formula below requires Id1=350 and Id2=400. What does Id1 and Id2 mean? Are those the upper and lower limits for Id's to be considered, so that someone with an Id of 375 would be included? Where are the Id numbers associated with each "Name" found?

MrExcel_20220803.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	Date	Id 1	Id 2	Name	Product	Units	State				ID lower	350
2	1/1/2022	100	200	Sam	AG	2.5	KS				ID upper	400
3	3/4/2021	600	700	Tim	EZ	1.3	OK				Product	I
4	5/6/2021	350	400	John	I	4	TX				State	TX
5	1/7/2021	500	700	Sam	AG	7.5	KS				Sum	4
6	3/4/2022	250	400	Tim	EZ	6	OK
7	5/1/2022	600	705	John	I	8	TX
Sheet2

Cell Formulas
Range	Formula
L5	L5	=SUMPRODUCT(INDEX($A$2:$I$99,,MATCH("Units",$A$1:$I$1,0)),(INDEX($A$2:$I$99,,MATCH("Product",$A$1:$I$1,0))=$L$3)*(INDEX($A$2:$I$99,,MATCH("State",$A$1:$I$1,0))=$L$4)*(INDEX($A$2:$I$99,,MATCH("Id 1",$A$1:$I$1,0))=$L$1)*(INDEX($A$2:$I$99,,MATCH("Id 2",$A$1:$I$1,0))=$L$2) )

Good Morning,
I forgot to mention, "Product" (Column E) may have more than one possibility of product (Ex. I, I+, or I-) and will need to sum all possibilities at times, so I tried to use a wildcard and it is not working with the formula, please help! ,,MATCH("Product",$A$1:$I$1,0))=$L$3&"*")* and this one ,,MATCH("Product",$A$1:$I$1,0))="*"&$L$3&"*")*