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

Tags:

Best Answer

Answers

  • Douglas Jerum
    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å
    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)

    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.

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    @Douglas Jerum

    I've updated the formula.

    Let me know that it works as expected.

    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.

  • Douglas Jerum
    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å
    Andrée Starå ✭✭✭✭✭✭

    @Douglas Jerum

    Excellent!

    You're more than welcome!

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!