Subtracting if drop down box says "Yes"

[niccc]

Hi all,

I would like some help with an excel spreadsheet I am creating to keep track of my overtime at work.

As seen in the attached file, Column D is the overtime that I worked on that day. Column E is a dropdown box (Yes,No) which identifies if I have used those overtime hours (Eg. On X day, I decided to finish work early and use my overtime hours from the previous day. In this instance, I would be selecting "Yes").

Cell D7 is equal to the total amount of overtime worked. I currently have =SUM(D3:D5) as the formula.

Cell D8 would be the actual total. Basically subtracting any overtime hours used.

I would like to know what formula I can use so that every time a cell in column E says "Yes", it would subtract the total amount of overtime in cell D7 and create a new value in D8 (actual total).

I tried to use the formula =IF(E3="Yes",D7-D3,D7) in cell D8, however, it only works for that specific row and not the ones below/after.

Thank you

 

[jtakw]

Hi,

Incorporate SUMIF:

Book3.xlsx
	A	B	C	D	E
1
2
3				2	No
4				10	Yes
5				2	Yes
6
7				14
8				2
Sheet990

Cell Formulas
Range	Formula
D7	D7	=SUM(D3:D5)
D8	D8	=D7-SUMIF(E3:E5,"Yes",D3:D5)

 

[niccc]

Hi,

Incorporate SUMIF:

Book3.xlsx
	A	B	C	D	E
1
2
3				2	No
4				10	Yes
5				2	Yes
6
7				14
8				2
Sheet990

Cell Formulas
Range	Formula
D7	D7	=SUM(D3:D5)
D8	D8	=D7-SUMIF(E3:E5,"Yes",D3:D5)

Thank you so much

 

[jtakw]

You're welcome, thanks for the feedback.