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]

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.

These are the only product code options (aaI, bI, 2I, I+, I- ) and the "*" did not include all "I" product codes, therefore, since I know the only options, could I do a list with search, ex. ISNUMBER(SEARCH({aaI, bI, 2I, I+, I-},INDEX($A$2:$I$99,,MATCH("Product",$A$1:$I$1,0))))

 

[KRice]

It's a little more complicated than that. Is that what you want to do?....search for any of multiple product codes for a given input of State and ID number? The text strings to search for will need to be enclosed in quotes, but the bigger issue is that you will generate a multi-column array, one column for each of those search terms. And if you only want to know that any one of those terms has been found, then you'll need to convert the multi-column array into a single column array. Let me know and I'll offer an approach.

 

[KRice]

Here is an example using an array of search terms. The MMULT function is needed to transform the multi-column array resulting from SEARCH into a single column array. To do that, we multiply that array by a single column array of 1's (which is little messy to do with an older version of Excel...but that's what this part of the formula does:

ROW(INDIRECT(1&":"&COUNTA({"aal","bl","2l","l+","l-"})))^0

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
4	5/6/2021	350	800	John	I	4	TX				State	TX
5	1/7/2021	500	700	Sam	aal	7.5	TX	x			Sum	23.5
6	3/4/2022	250	400	Tim	EZ	6	OK
7	5/1/2022	600	705	John	I-minus	8	TX
8	3/4/2022	250	400	Tim	aal	6	OK
9	5/1/2022	600	705	John	bl	8	TX	x
10	3/4/2022	250	400	Tim	l+	6	OK
11	5/1/2022	600	705	John	l-	8	TX	x
12	3/4/2022	250	400	Tim	EZ	6	OK
13	5/1/2022	600	705	John	I-minus	8	TX
14
Sheet2

Cell Formulas
Range	Formula
L5	L5	=SUMPRODUCT(INDEX($A$2:$I$13,,MATCH("Units",$A$1:$I$1,0)),MMULT(--ISNUMBER(SEARCH({"aal","bl","2l","l+","l-"},INDEX($A$2:$I$13,,MATCH("Product",$A$1:$I$1,0)))),ROW(INDIRECT(1&":"&COUNTA({"aal","bl","2l","l+","l-"})))^0)*(INDEX($A$2:$I$13,,MATCH("State",$A$1:$I$1,0))=$L$4)*(INDEX($A$2:$I$13,,MATCH("Id 1",$A$1:$I$1,0))<=$L$2)*(INDEX($A$2:$I$13,,MATCH("Id 2",$A$1:$I$1,0))>=$L$2) )

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:G13	Expression	=AND($B2<=$L$2,$C2>=$L$2,$G2=$L$4)	text	NO

I've added some conditional formatting to more easily pick out the matched rows and an "x" in column H identifies where the Product ID is found. Previously, I thought the letter that looks like a vertical line was a capital I as in "India", but is it a lower case case as in "like"?