A Single Formula to SUM a variable range of cells in a row

Douglas Jerum

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

Andrée Starå

Hi @Douglas Jerum

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 semi-colon 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!

Andrée Starå

Hi @Douglas Jerum

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 semi-colon 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!

Douglas Jerum

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

Andrée Starå

@Douglas Jerum

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)

Douglas Jerum

Invite sent!

Andrée Starå

@Douglas Jerum

I've updated the formula.

Let me know that it works as expected.

Douglas Jerum

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!

Andrée Starå

@Douglas Jerum

Excellent!

You're more than welcome!