SOLVED
Home

Computing the sum of a range based on a separate column with variable input values.

%3CLINGO-SUB%20id%3D%22lingo-sub-907547%22%20slang%3D%22en-US%22%3EComputing%20the%20sum%20of%20a%20range%20based%20on%20a%20separate%20column%20with%20variable%20input%20values.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-907547%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20to%20solve%20for%20a%20range%20of%20numbers%20based%20on%20a%20value%20I%20choose.%20For%20example%20in%20Column%20A%3A%20I%20have%20a%20depth%20values%20in%200.05%20m%20increments.%20I%20want%20to%20take%20a%20depth%20range%20from%20Column%20A%20(d-8b%20to%20d%2B4b%2C%20where%20d%20is%20depth%20and%20b%20is%20length%20value)%20then%20sum%20the%20cells%20in%20Column%20F%20that%20correspond%20to%20this%20range.%20d%20and%20b%20are%20in%20cells%20above%20the%20data%20so%20I%20can%20adjust%20them%20to%20suit%20my%20specific%20needs.%20I%20would%20like%20a%20cell%20that%20has%20a%20formula%20that%20gives%20me%20the%20sum%20of%20the%20values%20in%20column%20F%20that%20correspond%20to%20the%20depth%20range%20of%20d-8b%20to%20d%2B4b%20(from%20Column%20A).%20Please%2C%20if%20you%20have%20experience%20doing%20something%20like%20this%2C%20provide%20me%20with%20some%20insight.%20Please%20request%20clarification%20if%20my%20question%20is%20unclear.%20Thanks!%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-907547%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3Ehelp%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-907778%22%20slang%3D%22en-US%22%3ERe%3A%20Computing%20the%20sum%20of%20a%20range%20based%20on%20a%20separate%20column%20with%20variable%20input%20values.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-907778%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F424074%22%20target%3D%22_blank%22%3E%40jaretbull%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20attach%20your%20sample%20file%20with%20manually%20entered%20results%20and%20the%20logic%20of%20each%20result.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-910034%22%20slang%3D%22en-US%22%3ERe%3A%20Computing%20the%20sum%20of%20a%20range%20based%20on%20a%20separate%20column%20with%20variable%20input%20values.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-910034%22%20slang%3D%22en-US%22%3E%3CDIV%20class%3D%22lia-spoiler-container%22%3E%3CA%20class%3D%22lia-spoiler-link%22%20href%3D%22%23%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%20target%3D%22_blank%22%3ESpoiler%3C%2FA%3E%3CNOSCRIPT%3E(Highlight%20to%20read)%3C%2FNOSCRIPT%3E%3CDIV%20class%3D%22lia-spoiler-border%22%3E%3CDIV%20class%3D%22lia-spoiler-content%22%3E%26nbsp%3B%3C%2FDIV%3E%3CNOSCRIPT%3E%3CDIV%20class%3D%22lia-spoiler-noscript-container%22%3E%3CDIV%20class%3D%22lia-spoiler-noscript-content%22%3E%26nbsp%3B%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FNOSCRIPT%3E%3C%2FDIV%3E%3C%2FDIV%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20reply.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20find%20attached%20the%20sheet.%20Refer%20to%20Sheet2.%20I%20have%20included%20a%20photo%20in%20the%20sheet%20with%20my%20manual%20calculation.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20again%2C%3C%2FP%3E%3CP%3EJaret%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-910394%22%20slang%3D%22en-US%22%3ERe%3A%20Computing%20the%20sum%20of%20a%20range%20based%20on%20a%20separate%20column%20with%20variable%20input%20values.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-910394%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F424074%22%20target%3D%22_blank%22%3E%40jaretbull%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20attached%20version%20of%20your%20file%2C%20the%20formula%20in%20F3%20is%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DSUMIFS(F8%3AF1507%2CA8%3AA1507%2C%22%26gt%3B%3D%22%26amp%3BB2-(8*B3)%2C%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3EA8%3AA1507%2C%22%26lt%3B%3D%22%26amp%3BB2%2B(4*B3))%2F%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3E(((B2%2B(4*B3))-(B2-(8*B3)))%2FF2)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EPlease%20confirm%20if%20the%20foregoing%20formula%20returns%20your%20expected%20result.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-911311%22%20slang%3D%22en-US%22%3ERe%3A%20Computing%20the%20sum%20of%20a%20range%20based%20on%20a%20separate%20column%20with%20variable%20input%20values.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-911311%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20so%20much!%20I%20appreciate%20the%20help%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-911508%22%20slang%3D%22en-US%22%3ERe%3A%20Computing%20the%20sum%20of%20a%20range%20based%20on%20a%20separate%20column%20with%20variable%20input%20values.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-911508%22%20slang%3D%22en-US%22%3EYou%E2%80%99re%20very%20much%20welcome!%3C%2FLINGO-BODY%3E
jaretbull
New Contributor

I am trying to solve for a range of numbers based on a value I choose. For example in Column A: I have a depth values in 0.05 m increments. I want to take a depth range from Column A (d-8b to d+4b, where d is depth and b is length value) then sum the cells in Column F that correspond to this range. d and b are in cells above the data so I can adjust them to suit my specific needs. I would like a cell that has a formula that gives me the sum of the values in column F that correspond to the depth range of d-8b to d+4b (from Column A). Please, if you have experience doing something like this, provide me with some insight. Please request clarification if my question is unclear. Thanks! 

5 Replies

@jaretbull 

Please attach your sample file with manually entered results and the logic of each result. 

Spoiler
 

@Twifoo 

 

Thank you for your reply.

 

Please find attached the sheet. Refer to Sheet2. I have included a photo in the sheet with my manual calculation. 

 

Thanks again,

Jaret

Solution

@jaretbull 

In the attached version of your file, the formula in F3 is: 

=SUMIFS(F8:F1507,A8:A1507,">="&B2-(8*B3),
A8:A1507,"<="&B2+(4*B3))/
(((B2+(4*B3))-(B2-(8*B3)))/F2)

Please confirm if the foregoing formula returns your expected result. 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies