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

 

[thanksamillion101]

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

Hello,
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&"*")*

 

[KRice]

I think you'll need to use INDEX/MATCH to return to correct column, but then pass that column into a function that accepts a wildcard. For example, SEARCH...like 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	601
3	3/4/2021	600	700	Tim	EZ	1.3	OK				Product	I
4	5/6/2021	350	800	John	I+	4	TX				State	TX
5	1/7/2021	500	700	Sam	AG	7.5	KS				Sum	12
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)),ISNUMBER(SEARCH($L$3&"?",INDEX($A$2:$I$99,,MATCH("Product",$A$1:$I$1,0))))*(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) )

This assumes there is a single character after the product string. You can also incorporate a "*" if any number of characters could follow, but that might accept some products that should not be considered.

EDIT:
I'm concerned about mismatches with wildcards. Are the modifiers always single characters?...always at the end?...are they attached to the product code with a consistent character (hyphen, colon, etc.)?

 

[thanksamillion101]

I think you'll need to use INDEX/MATCH to return to correct column, but then pass that column into a function that accepts a wildcard. For example, SEARCH...like 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	601
3	3/4/2021	600	700	Tim	EZ	1.3	OK				Product	I
4	5/6/2021	350	800	John	I+	4	TX				State	TX
5	1/7/2021	500	700	Sam	AG	7.5	KS				Sum	12
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)),ISNUMBER(SEARCH($L$3&"?",INDEX($A$2:$I$99,,MATCH("Product",$A$1:$I$1,0))))*(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) )

This assumes there is a single character after the product string. You can also incorporate a "*" if any number of characters could follow, but that might accept some products that should not be considered.

EDIT:
I'm concerned about mismatches with wildcards. Are the modifiers always single characters?...always at the end?...are they attached to the product code with a consistent character (hyphen, colon, etc.)?

The modifiers will not always be single characters and will not always be at the ends, could be at the beginning as well, they are not attached with a consistent character. (aaI, bI, 2I, I+, I- )

 

[thanksamillion101]

I think you'll need to use INDEX/MATCH to return to correct column, but then pass that column into a function that accepts a wildcard. For example, SEARCH...like 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	601
3	3/4/2021	600	700	Tim	EZ	1.3	OK				Product	I
4	5/6/2021	350	800	John	I+	4	TX				State	TX
5	1/7/2021	500	700	Sam	AG	7.5	KS				Sum	12
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)),ISNUMBER(SEARCH($L$3&"?",INDEX($A$2:$I$99,,MATCH("Product",$A$1:$I$1,0))))*(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) )

This assumes there is a single character after the product string. You can also incorporate a "*" if any number of characters could follow, but that might accept some products that should not be considered.

EDIT:
I'm concerned about mismatches with wildcards. Are the modifiers always single characters?...always at the end?...are they attached to the product code with a consistent character (hyphen, colon, etc.)?

No errors are returned with this formula, but there is no sum returned and cell returns blank.

 

[KRice]

Select the cell with the formula. Then click in the formula bar, hit F2 to enter editing mode, and then click Ctrl-Shift-Enter simultaneously to reconfirm the formula as an array formula. You should see curly brackets automatically wrap around the formula. Does it work then?

 

[thanksamillion101]

Select the cell with the formula. Then click in the formula bar, hit F2 to enter editing mode, and then click Ctrl-Shift-Enter simultaneously to reconfirm the formula as an array formula. You should see curly brackets automatically wrap around the formula. Does it wor

 

[thanksamillion101]

Select the cell with the formula. Then click in the formula bar, hit F2 to enter editing mode, and then click Ctrl-Shift-Enter simultaneously to reconfirm the formula as an array formula. You should see curly brackets automatically wrap around the formula. Does it work then?

Yes that was done and formula is an array formula with brackets and cell still returns blank.

 

[KRice]

Have you installed the XL2BB add-in that will allow you to attach a small working sample of your worksheet? It's difficult to diagnose the problem without being able to see the issues. You can investigate the arrays produced by each of the logical tests to determine which one(s) are not working as expected. To do that, go back into the formula bar and select just the logical test to interrogate e.g., ISNUMBER(SEARCH($L$3&"?",INDEX($A$2:$I$99,,MATCH("Product",$A$1:$I$1,0))))
...and then hit F9 to see which rows have been identified as satisfying the criterion. When you are done looking, do not hit enter...just hit escape to preserve the array formula and prevent the intermediate exposed array from overwriting the formula.

 

[thanksamillion101]

Select the cell with the formula. Then click in the formula bar, hit F2 to enter editing mode, and then click Ctrl-Shift-Enter simultaneously to reconfirm the formula as an array formula. You should see curly brackets automatically wrap around the formula. Does it work then?

I replaced the "?" ISNUMBER(SEARCH($L$3&"?",INDEX($A$2:$I$99,,MATCH("Product",$A$1:$I$1,0)))) with an "*" and it worked! ISNUMBER(SEARCH($L$3&"*",INDEX($A$2:$I$99,,MATCH("Product",$A$1:$I$1,0))))

 

[KRice]

I'm glad you got it figured out...what terms were you looking for where this made a difference? The * is a wildcard for any number of characters. The risk is that if you search for I* and have products of I+ and I- and I that should be found (and none others), others such as AGI, AIBC, I65D, would be found too. That's why I'm wondering if SEARCH is the best approach, but without understanding what your actual product codes look like, I can't say one way or the other.