SUMIF Help

I am trying to write a formula to add up a range of cells but only add up the first and the last of the cells that show a numerical value.

So in the 3rd row it would add the 21 in the Monday and the 21 in the Wednesday column , in the 8th row it would add the 18 in the Tuesday column and the 18 in the Wednesday column, in the last row it would add the 18 in the Monday columns and the 18 in the Wednesday column.

Any help is greatly appreciated. Thank you,

Tags:

Best Answer

  • John_Foster
    John_Foster ✭✭✭✭✭✭
    Answer ✓

    Hi @Bwoods113,

    I think the below formula will give you what you are after. I have added a COUNTIF to see whether there are less than two cells with values, if there are it will always count only one, otherwise the formula I used would have added the first from the right and the first from the left which would have counted the cells twice.

    =IF(COUNTIF(A@row:F@row, <>0) < 2, IF(A@row <> 0, A@row, IF(B@row <> 0, B@row, IF(C@row <> 0, C@row, IF(D@row <> 0, D@row, IF(E@row <> 0, E@row, F@row))))), IF(A@row <> 0, A@row, IF(B@row <> 0, B@row, IF(C@row <> 0, C@row, IF(D@row <> 0, D@row, IF(E@row <> 0, E@row, F@row))))) + IF(F@row <> 0, F@row, IF(E@row <> 0, E@row, IF(D@row <> 0, D@row, IF(C@row <> 0, C@row, IF(B@row <> 0, B@row, A@row))))))

    What I have done is use column names A, B, C, etc rather than your column names.

    Hope this helps!

    John

Answers

  • KPH
    KPH ✭✭✭✭✭✭

    I'm not sure if this is the most efficient way to do it but I would JOIN all the columns into one cell with a | delimiter. Then I would split that up using FIND and the first | from the left and the first | from the right to identify the data you want. And do the math of those two parts. If the numbers are all two digits it will be easier. It should be possible to do this all with one formula but it will be quite long, so I would draft it in parts, test each part, and then join it together.

  • John_Foster
    John_Foster ✭✭✭✭✭✭
    Answer ✓

    Hi @Bwoods113,

    I think the below formula will give you what you are after. I have added a COUNTIF to see whether there are less than two cells with values, if there are it will always count only one, otherwise the formula I used would have added the first from the right and the first from the left which would have counted the cells twice.

    =IF(COUNTIF(A@row:F@row, <>0) < 2, IF(A@row <> 0, A@row, IF(B@row <> 0, B@row, IF(C@row <> 0, C@row, IF(D@row <> 0, D@row, IF(E@row <> 0, E@row, F@row))))), IF(A@row <> 0, A@row, IF(B@row <> 0, B@row, IF(C@row <> 0, C@row, IF(D@row <> 0, D@row, IF(E@row <> 0, E@row, F@row))))) + IF(F@row <> 0, F@row, IF(E@row <> 0, E@row, IF(D@row <> 0, D@row, IF(C@row <> 0, C@row, IF(B@row <> 0, B@row, A@row))))))

    What I have done is use column names A, B, C, etc rather than your column names.

    Hope this helps!

    John

  • Bwoods113
    Bwoods113 ✭✭✭

    @John Foster Thank you so much that worked perfect.

  • John_Foster
    John_Foster ✭✭✭✭✭✭

    You are very welcome @Bwoods113

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!