Need a formula please

[dal777]

Hi all,I'm in need of some help. As you can see from the image below I need a function/s in D4 that will total ( in this case ) B3 to B7. Because the lists vary in the number of rows a simple SUM(B3:B7) which I could copy and paste will not work, what I need is a more dynamic formula which will work out how many rows it should sum, when A=1 that is the last row that should be added to the sum. By the way is doesnt matter what values - if any end up in the D column cells except where C=0, hope that makes sense.
I will want to copy / paste the formula in D3 all the way down the D column.

The cells are colour coded for easier identifcation - a new colour is a new list

I'd been trying to work this out for a while but just cant figure it out so any help / suggestions would really be apprciated - thanks.


Click to see a sample of the sheet: http://img.photobucket.com/albums/v732/dal777/excel.jpg

Edited by Von Pookie ~ picture was kind of large

 

[adulted]

If Sum

Dal777
Try this in D3:
=IF(C3=0,"",SUM(B3:B21))
Double click the fill handle to copy down the remaining cells.
or
=IF(C3=0,"",SUM($B$3:$B$21))
if the column B range remains the same rows 3 to 21

Hope this is what your after
Paul

 

[barry houdini]

Try in D3 copied down

=IF(C3<>0,"",SUM(B3:INDEX(B3:B$100,MATCH(1,A3:A$100,0))))

Change 100 for a higher number if your data goes further

 

[dal777]

Barry - Yes it works, nice one, thanks , I'd never had have been able to figure that one out on my own, I'll now be able to sort a huge amount of data- thank you.



Regards.