Excel Sum & Sub Based On The Condition

[varunwalla]

Excel Sum & Sub Based On The Condition

• Need to sum in Column D if the value is Yes in Column B and the value should be taken from g1
• Need to sub in Column D if the value is No in Column B and the value should be taken from g1
• No changes in Column D6 as this dosn't have any condition like Yes or No

Please find the Expected result reference in column E

At the moment i am able to sum by using this in D2

=ROUND(MIN((C2*100),1000),2)+G1

and sub by using this in D4

=ROUND(MIN((C2*100),1000),2)-G1

Text Based Sum.xlsx
	A	B	C	D	E	F	G
1	Name	Increment	DA	Current Pay	Expected Result	Increment Amount	5
2	Ram	Yes	5	500	505
3	Prasad	Yes	5	500	505
4	Arjuan	No	5	500	495
5	Rajesh	No	5	500	495
6	Dinesh		5	500	500
Sheet1

Cell Formulas
Range	Formula
D2:D6	D2	=ROUND(MIN((C2*100),1000),2)

 

[Toadstool]

Hi Varunwalla,

Can you just use IF? This works as long as column B can only be empty, Yes or No.

Varunwalla.xlsx
	A	B	C	D	E	F	G
1	Name	Increment	DA	Current Pay	Expected Result	Increment Amount	5
2	Ram	Yes	5	500	505
3	Prasad	Yes	5	500	505
4	Arjuan	No	5	500	495
5	Rajesh	No	5	500	495
6	Dinesh		5	500	500
Sheet1

Cell Formulas
Range	Formula
D2:D6	D2	=ROUND(MIN((C2*100),1000),2)
E2:E6	E2	=IF(B2="",D2,IF(B2="yes",D2+$G$1,D2+($G$1*-1)))

 

[varunwalla]

Hi Varunwalla,

Can you just use IF? This works as long as column B can only be empty, Yes or No.

Varunwalla.xlsx
	A	B	C	D	E	F	G
1	Name	Increment	DA	Current Pay	Expected Result	Increment Amount	5
2	Ram	Yes	5	500	505
3	Prasad	Yes	5	500	505
4	Arjuan	No	5	500	495
5	Rajesh	No	5	500	495
6	Dinesh		5	500	500
Sheet1

Cell Formulas
Range	Formula
D2:D6	D2	=ROUND(MIN((C2*100),1000),2)
E2:E6	E2	=IF(B2="",D2,IF(B2="yes",D2+$G$1,D2+($G$1*-1)))

thanks for the reply but i want the result in column D not in column E

 

[Toadstool]

Is this better?

Varunwalla.xlsx
	A	B	C	D	E	F	G
1	Name	Increment	DA	Current Pay	Expected Result	Increment Amount	5
2	Ram	Yes	5	505
3	Prasad	Yes	5	505
4	Arjuan	No	5	495
5	Rajesh	No	5	495
6	Dinesh		5	500
Sheet1

Cell Formulas
Range	Formula
D2:D6	D2	=ROUND(MIN((C2*100),1000),2)+CHOOSE(MATCH(B2,{"Yes","No",0},0),$G$1,$G$1*-1,0)

 

[varunwalla]

Is this better?

Varunwalla.xlsx
	A	B	C	D	E	F	G
1	Name	Increment	DA	Current Pay	Expected Result	Increment Amount	5
2	Ram	Yes	5	505
3	Prasad	Yes	5	505
4	Arjuan	No	5	495
5	Rajesh	No	5	495
6	Dinesh		5	500
Sheet1

Cell Formulas
Range	Formula
D2:D6	D2	=ROUND(MIN((C2*100),1000),2)+CHOOSE(MATCH(B2,{"Yes","No",0},0),$G$1,$G$1*-1,0)

Awesome man it worked perfectly

 

[Toadstool]

Thanks for the feedback.