# count up in Column A until the next non-blank cell in Column C

1. ## count up in Column A until the next non-blank cell in Column C

Hello, I'm giving myself a headache and am hoping someone can bail me out...

I have values in Column A that need to Totaled in Column D when a new value is entered in Column C.

Attached is a small Excel file of sample data.

Sample Data.jpg

In the Sample Data, in cell D11, I would like to return the sum of range A3:A11.
In the Sample Data, in cell D14, I would like to return the sum of range A12:A14.

I think I'm dealing with an Offset formula, but am going in circles trying to resolve

2. ## Re: count up in Column A until the next non-blank cell in Column C

d2
``Please Login or Register  to view this content.``
try this and copy towards down

3. ## Re: count up in Column A until the next non-blank cell in Column C

or
d2
``Please Login or Register  to view this content.``
try this and copy towards down

4. ## Re: count up in Column A until the next non-blank cell in Column C

TRy

in D3 and copy down

=IF(\$C3="","",SUM(OFFSET(\$A\$2,MATCH(LOOKUP(10^10,\$C\$2:\$C2),\$C\$2:\$C2,0),0,ROWS(\$1:1)+2-MATCH(LOOKUP(10^10,\$C\$2:\$C2),\$C\$2:\$C2,0)-1)))

5. ## Re: count up in Column A until the next non-blank cell in Column C

JohnTopley: Thank you!! works like a charm...

6. ## Re: count up in Column A until the next non-blank cell in Column C

JohnTopley: The formula you provided does work perfectly, but I need help to make a modification. Turns out that numbers less than zero give an incorrect result in column "D". Can the formula be modified to ignore values <0, or treat them as 0?

For cell D14 in the sample data, the formula:

=IF(\$C3="","",SUM(OFFSET(\$A\$2,MATCH(LOOKUP(10^10,\$C\$2:\$C2),\$C\$2:\$C2,0),0,ROWS(\$1:1)+2-MATCH(LOOKUP(10^10,\$C\$2:\$C2),\$C\$2:\$C2,0)-1)))

should return 415.

The negative value in cell A12 changes 415 to -1994.

7. ## Re: count up in Column A until the next non-blank cell in Column C

Try

=IF(\$C3="","",SUMIF(OFFSET(\$A\$2,MATCH(LOOKUP(10^10,\$C\$2:\$C2),\$C\$2:\$C2,0),0,ROWS(\$1:1)+2-MATCH(LOOKUP(10^10,\$C\$2:\$C2),\$C\$2:\$C2,0)-1),">0"))

8. ## Re: count up in Column A until the next non-blank cell in Column C

Thanks John!! Once again you're a lifesaver!

There are currently 1 users browsing this thread. (0 members and 1 guests)