Find Conditional Percentage of sum of multiple cells

[SAJIL RAHMAN]

TYPE1	TYPE2	TOTAL1	TOTAL2	PERCENTAGE
E	BT	50000	50000	I NEED TO CALCULATE 3% OF SUM TOTAL1 &2 IF TYPE 1 IS "E/F/O" AND TYPE2 IS "DT"... IT SHOULD BE 5% IF TYPE1 IS "E" AND TYPE2 IS "BT"
E	DT	50000	50000
F	DT	150000	50000
O	DT	250000	50000

<tbody>
</tbody>

Please help me to find the suitable formula, i tried my best last 2 days....

 

[Aladin Akyurek]

In E2 enter and copy down:

=SUM(C2:D2)*INDEX({0.05,0.03;0,0.03;0,0.03},MATCH(A2,{"e";"o";"f"},0),MATCH(B2,{"bt","dt"},0))

 

[dave2018]

Assuming your data starts in A1, replace E2 with this formula and drag it down with the fill handle.

=IF(AND(A2="E",B2="BT"),SUM(C2:D2)*5%,SUM(C2:D2)*3%)

Does that work?

 

[dave2018]

Assuming your data starts in A1, replace E2 with this formula and drag it down with the fill handle.

=IF(AND(A2="E",B2="BT"),SUM(C2:D2)*5%,SUM(C2:D2)*3%)

Does that work?

Aladin, doing some "backwards" logic, since his criteria is simpler in his 2nd criteria example... But i love your formula's idea though

 

[SAJIL RAHMAN]

Aladin, doing some "backwards" logic, since his criteria is simpler in his 2nd criteria example... But i love your formula's idea though

Aladin's formula is good to confuse my friends, whereas Dave's is good to teach my friends...anyway many thanks for the great answers.

 

[dave2018]

My (our?) pleasure. Glad that you got your answer

 

[Aladin Akyurek]

Aladin's formula is good to confuse my friends, whereas Dave's is good to teach my friends...anyway many thanks for the great answers.

Row\Col	A​	B​	C​	D​	E​	F​	G​	H​	I​
1​	TYPE1	TYPE2	TOTAL1	TOTAL2	PERCENTAGE			bt	dt
2​	E	BT	50000​	50000​	5000​		e	5%​	3%​
3​	E	DT	50000​	50000​	3000​		o		3%​
4​	F	DT	150000​	50000​	6000​		f		3%​
5​	O	DT	250000​	50000​	9000​

In E2 enter and copy down:

Either [1]...

=SUM(C2:D2)*INDEX($H$2:$I$4,MATCH(A2,$G$2:$G$4,0),MATCH(B2,$H$1:$I$1,0))

Or [2]...

=SUM(C2:D2)*VLOOKUP(A2,$G$1:$I$4,MATCH(B2,$G$1:$I$1,0),0)

Or [3], apply F9 to the ranges of the INDEX bit in [1], and apply the result in E2 downwards...

=SUM(C2:D2)*INDEX({0.05,0.03;0,0.03;0,0.03},MATCH(A2,{"e";"o";"f"},0),MATCH(B2,{"bt","dt"},0))

Or [4], apply F9 to the ranges of the VLOOKUP bit in [2], and apply the result in E2 downwards...

=SUM(C2:D2)*VLOOKUP(A2,{0,"bt","dt";"e",0.05,0.03;"o",0,0.03;"f",0,0.03},MATCH(B2,${0,"bt","dt"},0),0)

I posted [3] for you as one that you could invoke (and confuse your friends).




It's thus your lookup table that you see in-lined in the formula...

 

[Aladin Akyurek]

Number [4] has a typo. Corrected for that...

=SUM(C2:D2)*VLOOKUP(A2,{0,"bt","dt";"e",0.05,0.03;"o",0,0.03;"f",0,0.03},MATCH(B2,{0,"bt","dt"},0),0)

 

[SAJIL RAHMAN]

My (our?) pleasure. Glad that you got your answer

Both has given my answer, while i have chosen your formula because it helps me to make some changes and to give more additional options.
Could you please help me that i need to ROUND the answer, i tried to change the formula but failed, please provide me the same above formula with Rounding facility. (>.5 to 1/ <.5 to 0)

 

[SAJIL RAHMAN]

row\col	a​	b​	c​	d​	e​	f​	g​	h​	i​
1​	type1	type2	total1	total2	percentage			bt	dt
2​	e	bt	50000​	50000​	5000​		e	5%​	3%​
3​	e	dt	50000​	50000​	3000​		o		3%​
4​	f	dt	150000​	50000​	6000​		f		3%​
5​	o	dt	250000​	50000​	9000​

<tbody>
</tbody>

in e2 enter and copy down:

Either [1]...

=sum(c2:d2)*index($h$2:$i$4,match(a2,$g$2:$g$4,0),match(b2,$h$1:$i$1,0))

or [2]...

=sum(c2:d2)*vlookup(a2,$g$1:$i$4,match(b2,$g$1:$i$1,0),0)

or [3], apply f9 to the ranges of the index bit in [1], and apply the result in e2 downwards...

=sum(c2:d2)*index({0.05,0.03;0,0.03;0,0.03},match(a2,{"e";"o";"f"},0),match(b2,{"bt","dt"},0))

or [4], apply f9 to the ranges of the vlookup bit in [2], and apply the result in e2 downwards...

=sum(c2:d2)*vlookup(a2,{0,"bt","dt";"e",0.05,0.03;"o",0,0.03;"f",0,0.03},match(b2,${0,"bt","dt"},0),0)

i posted [3] for you as one that you could invoke (and confuse your friends).




It's thus your lookup table that you see in-lined in the formula...

never knew that there are too many ways to get my answer....
thanks a lot got many things to learn....