SUMIF Help

Bwoods113

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,

John_Foster

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

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

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

@John Foster Thank you so much that worked perfect.

John_Foster

You are very welcome @Bwoods113