A Single Formula to SUM a variable range of cells in a row
Hi, I am trying to write a formula that will SUM between one and twenty columns in a row, based on the number in another cell in the same row.
If the number is 20, I want to sum all 20 cells in the row. If the number is 19, I want to sum the first 19 cells.
The range is always contiguous, and the cells to be summed always start with first cell in the range. The higher the number, the more cells to the right I want to sum.
So  Sum range A if criteria cell is X, Sum range B if criteria cell is Y, etc...I just can't figure out the right pair of functions to use and the syntax to put them together.
Thanks!
DJ
Best Answer

Try something like this. (Replace the column names to match yours)
=IF(Number@row = 1; SUM([1]@row); IF(Number@row = 2; SUM([1]@row:[2]@row);IF(Number@row = 3; SUM([1]@row:[3]@row);IF(Number@row = 4; SUM([1]@row:[4]@row);IF(Number@row = 5; SUM([1]@row:[5]@row);IF(Number@row = 6; SUM([1]@row:[6]@row);IF(Number@row = 7; SUM([1]@row:[7]@row);IF(Number@row = 8; SUM([1]@row:[8]@row);IF(Number@row = 9; SUM([1]@row:[9]@row);IF(Number@row = 10; SUM([1]@row:[10]@row);IF(Number@row = 11; SUM([1]@row:[11]@row);IF(Number@row = 12; SUM([1]@row:[12]@row);IF(Number@row = 13; SUM([1]@row:[13]@row);IF(Number@row = 14; SUM([1]@row:[14]@row);IF(Number@row = 15; SUM([1]@row:[15]@row);IF(Number@row = 16; SUM([1]@row:[16]@row);IF(Number@row = 17; SUM([1]@row:[17]@row);IF(Number@row = 18; SUM([1]@row:[18]@row);IF(Number@row = 19; SUM([1]@row:[19]@row);IF(Number@row = 20; SUM([1]@row:[20]@row)
The same version but with the below changes for convenience.
=IF(Number@row = 1, SUM([1]@row), IF(Number@row = 2, SUM([1]@row:[2]@row),IF(Number@row = 3, SUM([1]@row:[3]@row),IF(Number@row = 4, SUM([1]@row:[4]@row),IF(Number@row = 5, SUM([1]@row:[5]@row),IF(Number@row = 6, SUM([1]@row:[6]@row),IF(Number@row = 7, SUM([1]@row:[7]@row),IF(Number@row = 8, SUM([1]@row:[8]@row),IF(Number@row = 9, SUM([1]@row:[9]@row),IF(Number@row = 10, SUM([1]@row:[10]@row),IF(Number@row = 11, SUM([1]@row:[11]@row),IF(Number@row = 12, SUM([1]@row:[12]@row),IF(Number@row = 13, SUM([1]@row:[13]@row),IF(Number@row = 14, SUM([1]@row:[14]@row),IF(Number@row = 15, SUM([1]@row:[15]@row),IF(Number@row = 16, SUM([1]@row:[16]@row),IF(Number@row = 17, SUM([1]@row:[17]@row),IF(Number@row = 18, SUM([1]@row:[18]@row),IF(Number@row = 19, SUM([1]@row:[19]@row),IF(Number@row = 20, SUM([1]@row:[20]@row)
Depending on your country/region, you'll need to exchange the comma to a period and the semicolon to a comma.
Did that work?
I hope that helps!
Be safe and have a fantastic weekend!
Best,
Andrée Starå  Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå  Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com  E:andree@workbold.com  P: +46 (0)  72  510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Answers

Try something like this. (Replace the column names to match yours)
=IF(Number@row = 1; SUM([1]@row); IF(Number@row = 2; SUM([1]@row:[2]@row);IF(Number@row = 3; SUM([1]@row:[3]@row);IF(Number@row = 4; SUM([1]@row:[4]@row);IF(Number@row = 5; SUM([1]@row:[5]@row);IF(Number@row = 6; SUM([1]@row:[6]@row);IF(Number@row = 7; SUM([1]@row:[7]@row);IF(Number@row = 8; SUM([1]@row:[8]@row);IF(Number@row = 9; SUM([1]@row:[9]@row);IF(Number@row = 10; SUM([1]@row:[10]@row);IF(Number@row = 11; SUM([1]@row:[11]@row);IF(Number@row = 12; SUM([1]@row:[12]@row);IF(Number@row = 13; SUM([1]@row:[13]@row);IF(Number@row = 14; SUM([1]@row:[14]@row);IF(Number@row = 15; SUM([1]@row:[15]@row);IF(Number@row = 16; SUM([1]@row:[16]@row);IF(Number@row = 17; SUM([1]@row:[17]@row);IF(Number@row = 18; SUM([1]@row:[18]@row);IF(Number@row = 19; SUM([1]@row:[19]@row);IF(Number@row = 20; SUM([1]@row:[20]@row)
The same version but with the below changes for convenience.
=IF(Number@row = 1, SUM([1]@row), IF(Number@row = 2, SUM([1]@row:[2]@row),IF(Number@row = 3, SUM([1]@row:[3]@row),IF(Number@row = 4, SUM([1]@row:[4]@row),IF(Number@row = 5, SUM([1]@row:[5]@row),IF(Number@row = 6, SUM([1]@row:[6]@row),IF(Number@row = 7, SUM([1]@row:[7]@row),IF(Number@row = 8, SUM([1]@row:[8]@row),IF(Number@row = 9, SUM([1]@row:[9]@row),IF(Number@row = 10, SUM([1]@row:[10]@row),IF(Number@row = 11, SUM([1]@row:[11]@row),IF(Number@row = 12, SUM([1]@row:[12]@row),IF(Number@row = 13, SUM([1]@row:[13]@row),IF(Number@row = 14, SUM([1]@row:[14]@row),IF(Number@row = 15, SUM([1]@row:[15]@row),IF(Number@row = 16, SUM([1]@row:[16]@row),IF(Number@row = 17, SUM([1]@row:[17]@row),IF(Number@row = 18, SUM([1]@row:[18]@row),IF(Number@row = 19, SUM([1]@row:[19]@row),IF(Number@row = 20, SUM([1]@row:[20]@row)
Depending on your country/region, you'll need to exchange the comma to a period and the semicolon to a comma.
Did that work?
I hope that helps!
Be safe and have a fantastic weekend!
Best,
Andrée Starå  Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå  Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com  E:andree@workbold.com  P: +46 (0)  72  510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

First of all, thank you so much. This is a heroic effort. I'm almost there, but it's not quite working. It works when the result is '1' (which is just a single cell), and '2', which is simply adding two individual cells (not a range). The issue is the ranges. The formula is not producing an error, but the cell with the result comes up blank if the number is 3 or more. Screen shot of my implementation of your formula attached. Feels like I've got some simple syntax error that I just can't see.
Can you see where I'm going wrong?
DJ

Happy to help!
I'd be happy to take a quick look.
Can you maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå  Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com  E:andree@workbold.com  P: +46 (0)  72  510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Invite sent!

SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå  Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com  E:andree@workbold.com  P: +46 (0)  72  510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Andree, the fix was spot on! replacing the '+' with a comma to sum the range and the single cell did the trick.
Thank you so much for this help!

SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå  Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com  E:andree@workbold.com  P: +46 (0)  72  510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Help Article Resources
Categories
Check out the Formula Handbook template!