CODEWITHSUNDEEP
excelvbaexcel-formula
Lester
Lester

Reputation: 9

Sum numbers in between blank cells in Excel

I have a column of numbers in Excel. In this column, there can be blank cells after any number, but there are never two blank cells in a row. I want to take the sum of the numbers in between these blank cells, and after every blank cell, the counter basically resets and the function takes the sum of the other set of numbers that are in bewteen blank cells. I want the in between blank cells numbers to behave as its "own system" in the sense that the cumulative sum of one set of numbers that are inturrupted by blank cells do not affect the next set of numbers.

The attached image basically gives a better example. B2:B6 is the sum of A2:A6, then A8:A10 is the sum of B8:B10 and finally B7:B15 is the sum of A7:A15. This formula should work even if I keep on adding numbers and blank cells to column A.

Ideally, I would like the formula to be in the Excel spreadsheet I'm using, but it could also be in VBA. Thanks! Screenshot for my Excel sheet example

I've tried to add an intermediate column where it does a cumulative sum. On B2 the formula is =IF(ISBLANK(A2), "", IF(ISBLANK(A1), A2, B1 + A2)).

This basically checks if the previous and next cells are blank, and if not, it takes the sum of the previous cell on column B and the cell being referenced on row A. This is pretty straightforward, but I haven't been able to decifer how to copy the last value of this cumulative sum to all other cells above.

Upvotes: 0

Views: 2295

Answers (2)

DS_London
DS_London

Reputation: 4261

If the input range is A2:A12, this is an alternative without using CSE:

=LET(x,A2:A12,
     n,SCAN(1,x,LAMBDA(a,v,IF(ISNUMBER(v),a,a+1))),
     IF(ISNUMBER(x),BYROW(n,LAMBDA(r,SUM(FILTER(x,n=r)))),""))

(if you have an up-to-date version of Excel)

There may be more elegant ways, but I couldn't get SUMIF() to work within the LET() function.

Upvotes: 2

ΑΓΡΙΑ ΠΕΣΤΡΟΦΑ
ΑΓΡΙΑ ΠΕΣΤΡΟΦΑ

Reputation: 1672

enter image description here

Copy this formula to B2, press CTRL + SHIFT + ENTER and drag down.
Customize A2:$A$1000 according to your needs

=IF(A2="";"";IF(ISNUMBER(B1);B1;SUM(A2:INDEX(A2:$A$1000;MATCH(TRUE;(A2:$A$1000="");0)))))

The array formula is calculated only on the first number of each block and is copied to the others

Upvotes: 2

Related Questions