Need Help with a Formula

%3CLINGO-SUB%20id%3D%22lingo-sub-2064768%22%20slang%3D%22en-US%22%3ENeed%20Help%20with%20a%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2064768%22%20slang%3D%22en-US%22%3E%3CP%3EI%20need%20to%20create%20a%20formula%20that%20incrementally%20calculates%20values%20in%20a%20cell%20based%20on%20values%20in%20cells%20contained%20in%20a%20different%20worksheet.%20For%20example%2C%20if%20the%20value%20in%20'Tracking'!H2%3DApproved%2C%20then%20add%2040%20to%20cell%20K5%20but%20do%20nothing%20if%20the%20value%20is%20not%20%3DApproved%20and%2C%20if%20the%20value%20in%20'Tracking'!H3%3DApproved%20but%20do%20nothing%20if%20the%20value%20is%20not%20%3DApproved%2C%20then%20add%20another%2040%20to%20cell%20K5%2C%20and%20so%20on.%20This%20would%20create%20a%20running%20total%20in%20cell%20K5%20as%20the%20values%20in%20the%20specified%20cells%20in%20the%20'Tracking'%20worksheet%20are%20changed%20to%20Approved.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20can%20get%20this%20to%20work%20on%20one%20cell%20using%20the%20%22IF%22%20function%20but%20am%20not%20savvy%20enough%20to%20figure%20out%20how%20to%20string%20the%20argument%20together%20to%20add%20more%20data%20as%20the%20other%20cells%20are%20changed.%20ANY%20assistance%20will%20be%20greatly%20appreciated!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2064768%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2064921%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20Help%20with%20a%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2064921%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F930692%22%20target%3D%22_blank%22%3E%40rchazen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPerhaps%20something%20like%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DSUM(40*(Tracking!%24C%241%3A%24C%2420%3D%22Approved%22))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EUse%20SUMPRODUCT()%20instead%20of%20SUM()%20if%20you%20are%20on%20Excel%20without%20dynamic%20array%20support.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2065725%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20Help%20with%20a%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2065725%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat%20worked%20perfectly!%20Sometimes%20the%20simplest%20solution%20is%20right%20in%20front%20of%20us.%20Thank%20you%20for%20the%20quick%20response%20and%20right%20answer!%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I need to create a formula that incrementally calculates values in a cell based on values in cells contained in a different worksheet. For example, if the value in 'Tracking'!H2=Approved, then add 40 to cell K5 but do nothing if the value is not =Approved and, if the value in 'Tracking'!H3=Approved but do nothing if the value is not =Approved, then add another 40 to cell K5, and so on. This would create a running total in cell K5 as the values in the specified cells in the 'Tracking' worksheet are changed to Approved.

 

I can get this to work on one cell using the "IF" function but am not savvy enough to figure out how to string the argument together to add more data as the other cells are changed. ANY assistance will be greatly appreciated!

3 Replies

@rchazen 

Perhaps something like

=SUM(40*(Tracking!$C$1:$C$20="Approved"))

Use SUMPRODUCT() instead of SUM() if you are on Excel without dynamic array support.

@Sergei Baklan 

That worked perfectly! Sometimes the simplest solution is right in front of us. Thank you for the quick response and right answer!

@rchazen , you are welcome