Create Total with Match

[kiyo052]

I need a formula for J8 based on the information from J1:J7 that would be based on the information from A1:F7

J1= dropdown menu of currency
J2= Manual number someone inputs
J3:J7= dropdown menu of Y or N

If J3= Yes, than J3=J2 (example, if J2= 3 and J3= Y than the total would be 6 because 3+3=6)
If J4= Yes, than J4=J2 (example, if J2= 3 and J4= Y than the total would be 6 because 3+3=6)

Example 1:
J1= USD
J2= 3
J3 = Y
J4= N
J5= N
J6= N
J7= N

Then J8= 1,044 because J2= 3*300 and J3= 3*48

Example 2:
J1= USD
J2= 5
J3 = N
J4= Y
J5= N
J6= Y
J7= N

Then J8=5,980 because J2= 5*300, J4=96*5, and J6=4000

	A	B	C	D	E	F	G	H	I	J	K
1	Packages	USD	AUD	CAD	EUR	GBP			Currency	USD
2	Users	300	300	300	288	240			Number of Users	5
3	A	48	48	48	48	36			A	N
4	B	96	96	96	96	84			B	N
5	C	2000	2200	2200	1900	1600			C	N
6	D	4000	4400	4400	3800	3200			D	N
7	E	8800	8800	8800	8400	7050			E	N
8									Total
9
10
11

<tbody>
</tbody>

Please let me know if this is at all possible.

Thank you in advance if anyone can figure out this complicated formula!

 

[Drrellik]

	A	B	C	D	E	F	G	H	I	J	K
1	Packages	USD	AUD	CAD	EUR	GBP			Currency	usd
2	Users	300	300	300	288	240			Number of Users	5	=IF(J2="","",INDEX($B$2:$F$2,,MATCH($J$1,$B$1:$F$1,0))*$J$2)
3	A	48	48	48	48	36			A	n	=IF(J3="y",INDEX($B$3:$F$3,,MATCH($J$1,$B$1:$F$1,0))*$J$2,"")
4	B	96	96	96	96	84			B	y	=IF(J4="y",INDEX($B$4:$F$4,,MATCH($J$1,$B$1:$F$1,0))*$J$2,"")
5	C	2000	2200	2200	1900	1600			C	n	=IF(J5="y",INDEX($B$5:$F$5,,MATCH($J$1,$B$1:$F$1,0))*$J$2,"")
6	D	4000	4400	4400	3800	3200			D	y	=IF(J6="y",INDEX($B$6:$F$6,,MATCH($J$1,$B$1:$F$1,0))*$J$2,"")
7	E	8800	8800	8800	8400	7050			E	n	=IF(J7="y",INDEX($B$7:$F$7,,MATCH($J$1,$B$1:$F$1,0))*$J$2,"")
8									=SUM(K2:K7)
9
10
11

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula K2 =IF(J2="","",INDEX($B$2:$F$2,,MATCH($J$1,$B$1:$F$1,0))*$J$2) K3 =IF(J3="y",INDEX($B$3:$F$3,,MATCH($J$1,$B$1:$F$1,0))*$J$2,"") K4 =IF(J4="y",INDEX($B$4:$F$4,,MATCH($J$1,$B$1:$F$1,0))*$J$2,"") K5 =IF(J5="y",INDEX($B$5:$F$5,,MATCH($J$1,$B$1:$F$1,0))*$J$2,"") K6 =IF(J6="y",INDEX($B$6:$F$6,,MATCH($J$1,$B$1:$F$1,0))*$J$2,"") K7 =IF(J7="y",INDEX($B$7:$F$7,,MATCH($J$1,$B$1:$F$1,0))*$J$2,"") I8 =SUM(K2:K7) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

This is probably sloppy but if you don't mind using helper cells to look for the type of currency and then look for a value you can try this.

~DR

 

[jtakw]

Example 2:
J1= USD
J2= 5
J3 = N
J4= Y
J5= N
J6= Y
J7= N

Then J8=5,980 because J2= 5*300, J4=96*5, and J6=4000

	A	B	C	D	E	F	G	H	I	J	K
1	Packages	USD	AUD	CAD	EUR	GBP			Currency	USD
2	Users	300	300	300	288	240			Number of Users	5
3	A	48	48	48	48	36			A	N
4	B	96	96	96	96	84			B	N
5	C	2000	2200	2200	1900	1600			C	N
6	D	4000	4400	4400	3800	3200			D	N
7	E	8800	8800	8800	8400	7050			E	N
8									Total
9
10
11

<tbody>
</tbody>

Hi,

I'm not understanding your sample result highlighted in Red above, why isn't J6 of 4000 multiplied by 5?
Assuming that's a typo, the following will work, I've included Both your sample datasets:

Book1
	A	B	C	D	E	F	G	H	I	J
1	Packages	USD	AUD	CAD	EUR	GBP			Currency	USD
2	Users	300	300	300	288	240			Number of Users	5
3	A	48	48	48	48	36			A	N
4	B	96	96	96	96	84			B	Y
5	C	2000	2200	2200	1900	1600			C	N
6	D	4000	4400	4400	3800	3200			D	Y
7	E	8800	8800	8800	8400	7050			E	N
8									Total	21980
9
10	Packages	USD	AUD	CAD	EUR	GBP			Currency	USD
11	Users	300	300	300	288	240			Number of Users	3
12	A	48	48	48	48	36			A	Y
13	B	96	96	96	96	84			B	N
14	C	2000	2200	2200	1900	1600			C	N
15	D	4000	4400	4400	3800	3200			D	N
16	E	8800	8800	8800	8400	7050			E	N
17									Total	1044
Sheet14

Cell Formulas
Range	Formula
J8	=SUMPRODUCT(SUM((B1:F1=J1)*(B2:F2)*J2)+SUM((B1:F1=J1)*(J3:J7="Y")*(B3:F7)*J2))
J17	=SUMPRODUCT(SUM((B10:F10=J10)*(B11:F11)*J11)+SUM((B10:F10=J10)*(J12:J16="Y")*(B12:F16)*J11))

 

[Drrellik]

Nicely Done,

there is always more than one way in excel that's why I love learning more and more every time I help, I learn too.

~DR