Lookup first Non zero cell and sum of all cells in the adjacent cell

[Aziawan]

Hi Guys,

I have been trying to put this formula together but have been successful so far. I have used "match, Index and row function but can't get the sum right.

I have 4 columns A, B, C and D which is Day, Date, Amount and Refils respectfully.

So I want to lookup first non zero number in Column D "which is Refil" and return the sum of all the column in the adjacent column which is "Amount" till that row.

Example is: We want to lookup first non zero number and for instance it is on cell D29 then I require the sum of C2:C29. The formula should identify itself the first non zero number in column D everytime the sheet is updated and should return the sum of the cells in column C respectfully.

Your help will be much appreciated.

Cheers

Azi
Excel Threads.xls

[JieJenn]

try

=SUM(INDIRECT("$C$2:$C"&MATCH(SUMIF($D$2:$D$36,">"&0.01,$D$2:$D$36),$D$2:$D$36,0)))

[dredwolf]

Is ROWS() available in 2003?

if so you could use something like:

=IF(D2>0,SUM(OFFSET($C$2,ROW()-1,0,ROWS($C$2:$C$'last row of C")-(ROW()-2))),"")

you would need to blank out any following rows, and this assumes the data starts in row 2...but should get you started at least

Hope this Helps

[Aziawan]

Thankyou so much for that.
I forgot to mention one thing that this sheet is part of a cost model and it is also linked with SQL which is updating information everytime we enter different sites. The formula turns into #N/A or 0 when I change the site and data updates. Also there can be multiple amounts in Column D but I want the first Non Zero to appear.

Cheers

[Aziawan]

@dredwolf
Thankyou so much as well for your response,

The formula appears with Blank cell as well and no results.

Am I making any mistake? The sheet that I attached in my first post is exactly the same but obviously it is connected with SQL and hence we change the site name and the data changes but it should not matter I believe as only cells should change.

Any thoughts?

Cheers mate

[dredwolf]

OK, I've uploaded a sample of what I am doing..see if that works for you..got rid of the following sums as well..

Edit
Revised formula:

=IF(COUNT($E$1:E1)=0,IF(D2*1>0,SUM(OFFSET($C$1,ROW()-1,0,ROWS(data)-(ROW()-2))),""),"")

Hope this helps

Edit2

aah...sorry, missed the upload, getting blinder and can barely distinguish the color change, will try my formula on that

[dredwolf]

well, I'm not reaaly sure, it works fine here, maybe you could upload a sample ?
and I'm not sure if 2003 supports the ROWS() function, so that could be a problem ( although I'd expect to see a #NAME error in that case)...

[dredwolf]

okay, looks like 2003 does not support ROWS(), so try this instead, it will give you 10000 rows of data to work with (up it if you need too )

=IF(COUNT($E$1:E1)=0,IF(D2*1>0,SUM(OFFSET($C$1,ROW()-1,0,10000-(ROW()-2))),""),"")

[dredwolf]

See attachment, it is working for me here(compatibility mode), so see if it does for you

Hope this works...lol