Question Locked

How can I sum by referencing a blank cell as the starting point to sum?

0 helpful
2 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I am trying to sum contigous cells but separated by blank cells. I use the blank cell as a reference pt.

e.g.
a1 = blank
a2 = 2
a3 = 4
a4 = 7
a5 = blank
a6 = 2
a7 = 5
a8 = 3
a9 = 10
a10 = blank
a11 = 7
a12 = 9
a13 = 20
a14 = 35
a15 = 1
a16 = 3
a17 = blank
Using an if statement I would like to use the blank as a true statement but only sum the values between the blanks. The statement function will be in the next column of the same row.

The if function would look like this - IF(Cell=" ",Sum(Blank Cell:Upto non-blank cell)," ")). The cells being summed are in the same column but are separated by blank cells. I would like to copy and paste the formula; therefore, I can not use a cell range because the ranges varies.(See Above for data example)

Is there a way to do this in Excel function? If not how can I reference cells in VBA that are not consistent in their ranges using a loop, if-then, or case syntax?

2 replies

by woozlemonk

I would also very much like to know how to identify the next blank cell
only I want to use it to end the string of a sum as in

sum([cell right below this one]:[next blank cell])

that way I could have totals list on the top of the list of items beneath it

this is part of a larger code that pertains
to the entire column.
I could completely automate the column and reduce the amount of input required as I would subsequently be able to reproduce the same code for several other columns

This discussion is closed to new replies.