 • 507K Members
• 3,209 Online
• 603K Conversations
SOLVED

# Subtract a numeric value from the last populated numeric cell in a colum if another cell has a text

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

3 Replies
Solution

# Re: Subtract a numeric value from the last populated numeric cell in a colum if another cell has a t

@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;;

# Re: Subtract a numeric value from the last populated numeric cell in a colum if another cell has a t

This looks fantastic thank you. Now just to try and understand how it works :)

# Re: Subtract a numeric value from the last populated numeric cell in a colum if another cell has a t

@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.

Related Conversations
Problems with formating text within a cell.
Maciej Fox in Excel on
10 Replies
Conditional Formatting - Consider Only Value as Lowest Value
Ben Smith in Excel on
2 Replies
Help with an IF AND formula
aanaya6 in Excel on
3 Replies
IF FUNCTION ISN'T WORKING NO MATTER HOW SIMPLE THE COMMAND IS
thomasea in Excel on
6 Replies
not able to type number and text in same cell?
Deleted in Excel on
2 Replies