#### dal777

##### New Member
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

### Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

##### Active Member
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

##### MrExcel MVP
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

##### New Member
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.

Replies
4
Views
130
Replies
1
Views
145
Replies
5
Views
101
Replies
1
Views
125
Replies
0
Views
77