SOLVED
Home

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

%3CLINGO-SUB%20id%3D%22lingo-sub-666976%22%20slang%3D%22en-US%22%3ESubtract%20a%20numeric%20value%20from%20the%20last%20populated%20numeric%20cell%20in%20a%20colum%20if%20another%20cell%20has%20a%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-666976%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20attached%20time%20sheet%2C%20I%20am%20trying%20to%20work%20a%20formula%20that%20will%20subtract%20the%20numeric%20value%20of%20Column%20B%20from%20the%20last%20numeric%20Value%20in%20column%20I%20where%20column%20H%20as%20%22H%22%20populated.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(H3%3D%22H%22%2C%24L%247-B3%2C%22%22)%20brings%20the%20initial%20value%20for%20my%20calculations%20forward%20if%20%22H%22%20is%20in%20column%20H.%20The%20challenge%20is%20to%20use%20the%20latest%20value%20in%20Column%20I%20as%20the%20number%20to%20be%20subtracted%20from%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-666976%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20Online%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-667032%22%20slang%3D%22en-US%22%3ERe%3A%20Subtract%20a%20numeric%20value%20from%20the%20last%20populated%20numeric%20cell%20in%20a%20colum%20if%20another%20cell%20has%20a%20t%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-667032%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F354243%22%20target%3D%22_blank%22%3E%40AJMack82%3C%2FA%3E%26nbsp%3B%2C%20that%20could%20be%3C%2FP%3E%0A%3CPRE%3E%3DIF(%24H4%3D%22H%22%2C%24I%243-SUMIF(%24H%244%3A%24H4%2C%22H%22%2C%24B%244%3AB4)%2C%22%22)%3C%2FPRE%3E%0A%3CP%3Eor%20even%20better%3C%2FP%3E%0A%3CPRE%3E%3DIF(%24H4%3D%22H%22%2C%24I%243-SUMIF(%24H%244%3A%24H4%2C%22H%22%2C%24B%244%3AB4)%2C0)%3C%2FPRE%3E%0A%3CP%3Ewith%20custom%20format%20%5Bh%5D%3Amm%3B%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-667053%22%20slang%3D%22en-US%22%3ERe%3A%20Subtract%20a%20numeric%20value%20from%20the%20last%20populated%20numeric%20cell%20in%20a%20colum%20if%20another%20cell%20has%20a%20t%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-667053%22%20slang%3D%22en-US%22%3EThis%20looks%20fantastic%20thank%20you.%20Now%20just%20to%20try%20and%20understand%20how%20it%20works%20%3A)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-667096%22%20slang%3D%22en-US%22%3ERe%3A%20Subtract%20a%20numeric%20value%20from%20the%20last%20populated%20numeric%20cell%20in%20a%20colum%20if%20another%20cell%20has%20a%20t%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-667096%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F354243%22%20target%3D%22_blank%22%3E%40AJMack82%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome.%20For%20the%20current%20row%20if%20in%20I%20is%20%22H%22%20you%20sum%20values%20in%20column%20B%20starting%20from%20%24H%244%20till%20current%20row%20if%20only%20in%20column%20I%20for%20the%20same%20rows%20you%20have%20%22H%22%20and%20subtract%20result%20from%20%24I%243.%3C%2FP%3E%3C%2FLINGO-BODY%3E
AJMack82
New Contributor

 

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

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

 

 

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

@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