Index match with non unique values

[juanitadeb]

0000378G	FCP1 or FCP4	€ 39.44	0​	€ -	0​	€ -
0000447G	0​	€ -	0​	€ -	0​	€ -
0000450G	0​	€ -	0​	€ -	0​	€ -
0000458G	2.01.03	€ 70.47	2.01.07.01.02	€ 375.75	2.01.07.01.02	€ 228.13

So this is example of my data.

Now i want the addition of the amount for each value (2.01.07.01.02) for each ID.

I tried with IFNA(INDEX(TRANSPOSE(Sheet2!4:4),MATCH("2.04.02",TRANSPOSE(Sheet2!4:4),0)+1,1),0) but it was only finding the first one.

Is there a way to do this please?

 

[Legacy 143009]

ID

PRODUCT

PRICE

PRODUCT

PRICE

PRODUCT

PRICE

PRODUCT

PRICE

Total of 2.01.07.01.02

10732120

2.01.02

€ 2,914.26

2.01.07.01.02

€ 5,018.89

2.01.07.01.02

€ 4,025.85

2.01.07.01.02

€ 18,277.35

€27322.09

My problem is with the last column.
Is this version ok or should I uploaded somewhere else? I am sorry but i do not know how to use the website.
Thank you so much.

Have you tried my solution at post#9?

 

[juanitadeb]

A more compact alternative:

=SUM(SUMIF(A4:AD4,{"2.01.03","2.01.07.01.02"},OFFSET(A4:AD4,0,1)))

I got a reference error

 

[Legacy 143009]

I got a reference error

Try to start from the column referance where you begin to search for your condition.

=SUM(SUMIF(B4:AD4,{"2.01.03","2.01.07.01.02"},OFFSET(B4:AD4,0,1)))

What are the address of ID column and address of Total column?

 

[Peter_SSs]

Is this version ok or should I uploaded somewhere else? I am sorry but i do not know how to use the website.

That version is better than nothing but the best way is to use XL2BB, as I have done below. I provided a link for XL2BB in post #4 and there are instructions if you follow that link.

Based on your sample in post #10, see if this is what you want.
You may have to adjust the ranges in the formula to suit your data but note that the two ranges are offset by one column.

22 10 17.xlsm
	A	B	C	D	E	F	G	H	I	J
1	ID	PRODUCT	PRICE	PRODUCT	PRICE	PRODUCT	PRICE	PRODUCT	PRICE	Total of 2.01.07.01.02
2	10732120	2.01.02	€ 2,914.26	2.01.07.01.02	€ 5,018.89	2.01.07.01.02	€ 4,025.85	2.01.07.01.02	€ 18,277.35	27,322.09
Product Sum

Cell Formulas
Range	Formula
J2	J2	=SUMIF(B2:H2,"2.01.07.01.02",C2:I2)

 

[juanitadeb]

Try to start from the column referance where you begin to search for your condition.

=SUM(SUMIF(B4:AD4,{"2.01.03","2.01.07.01.02"},OFFSET(B4:AD4,0,1)))

What are the address of ID column and address of Total column?

ID is in column A and Total column CR

 

[juanitadeb]

ID is in column A and Total column CR

It worked on a row where i had only 1 entry for that value but others i got a reference error

 

[juanitadeb]

That version is better than nothing but the best way is to use XL2BB, as I have done below. I provided a link for XL2BB in post #4 and there are instructions if you follow that link.

Based on your sample in post #10, see if this is what you want.
You may have to adjust the ranges in the formula to suit your data but note that the two ranges are offset by one column.

22 10 17.xlsm
	A	B	C	D	E	F	G	H	I	J
1	ID	PRODUCT	PRICE	PRODUCT	PRICE	PRODUCT	PRICE	PRODUCT	PRICE	Total of 2.01.07.01.02
2	10732120	2.01.02	€ 2,914.26	2.01.07.01.02	€ 5,018.89	2.01.07.01.02	€ 4,025.85	2.01.07.01.02	€ 18,277.35	27,322.09
Product Sum

Cell Formulas
Range	Formula
J2	J2	=SUMIF(B2:H2,"2.01.07.01.02",C2:I2)

Thank you very much for your help! Yours worked as well.

 

[juanitadeb]

Try to start from the column referance where you begin to search for your condition.

=SUM(SUMIF(B4:AD4,{"2.01.03","2.01.07.01.02"},OFFSET(B4:AD4,0,1)))

What are the address of ID column and address of Total column?

It worked sorry I was taking the column of id within the array aswell. Thank you for your help!

 

[Legacy 143009]

It worked sorry I was taking the column of id within the array aswell. Thank you for your help!

I am glad that you solved the issue Have a nice day!

 

[Peter_SSs]

I notice that you have marked post #13 as the solution. Be aware that OFFSET is a volatile function so I would recommend avoiding such formulas where possible. If looking up multiple values in the same row then the same result can be achieved with this non-volatile (& simpler) alternative

=SUM(SUMIF(B4:AD4,{"2.01.03","2.01.07.01.02"},C4:AE4))