Jun 04 2019 04:03 AM
In the attached time sheet, I am trying to work a formula that will subtract the numeric value of Column B from the last numeric Value in column I where column H as "H" populated.
=IF(H3="H",$L$7-B3,"") brings the initial value for my calculations forward if "H" is in column H. The challenge is to use the latest value in Column I as the number to be subtracted from
Jun 04 2019 04:25 AM
Solution@AJMack82 , that could be
=IF($H4="H",$I$3-SUMIF($H$4:$H4,"H",$B$4:B4),"")
or even better
=IF($H4="H",$I$3-SUMIF($H$4:$H4,"H",$B$4:B4),0)
with custom format [h]:mm;;
Jun 04 2019 04:33 AM
Jun 04 2019 04:47 AM
@AJMack82 , you are welcome. For the current row if in I is "H" you sum values in column B starting from $H$4 till current row if only in column I for the same rows you have "H" and subtract result from $I$3.
Jun 04 2019 04:25 AM
Solution@AJMack82 , that could be
=IF($H4="H",$I$3-SUMIF($H$4:$H4,"H",$B$4:B4),"")
or even better
=IF($H4="H",$I$3-SUMIF($H$4:$H4,"H",$B$4:B4),0)
with custom format [h]:mm;;