Complex sum value

So, due to the resounding success of support yesterday I'm back for more!

I have a long list (around 40,000 rows) made up of approx 50 reference numbers in Column B. Each reference number is repeated a different number of times and in column F is data relating to the reference number.

I am trying to write a formula which will give me the single reference number from column B and then the SUM of all related cells in F relevant to that number. In my head I was thinking some kind of vlookup until number changes and then a sum formula but no idea either where to start or if this is possible.

Any help gratefully received as always.

Thanks
John

Slight confusion on your request since it looks like you want the formula to do two things: give you the single reference number, and a sum. I assume you meant that the reference number is input to the formula and it provides the sum. Does this work?

=SUMIF($B$2:$B$9,B2,$F$2:$F$9)

Put that at the top of your table (the above assumes your B column range is B2:B9 and the F column range is F2:F9). Then drag that formula all the way down to the bottom of your table. The lack of the '$' around the middle B2 means it will change as you drag.

Pauley

Thanks for the quick reponse Pauley. I think the problem with that formula is that I don't know the extent of the SUM table (in the example you use B2:B9) but these numbers will change as I go through the sheet and if I knew where they stopped I would just use the sum function to work it out. I was hoping for a formula which would recognise the fact the number in column B had changed and therefore add up all values which are associated with that number. If there was a way to attach the file I would as think it would explain the problem better than I have.

I think I may have cracked it with a terribly simple pivot table!