Need a formula please

dal777

New Member
Joined
Dec 6, 2005
Messages
5
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
 

Some videos you may like

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

adulted

Active Member
Joined
Jan 22, 2004
Messages
339
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
Joined
Mar 23, 2005
Messages
20,825
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
Joined
Dec 6, 2005
Messages
5
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.


:biggrin:
Regards.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,055
Messages
5,569,946
Members
412,299
Latest member
agentless
Top