# How to sum cells depending on the content of a neighbor cell

I have an Excel document with the following columns;

``````Date      |   Reference    |    Amount
23/01/11  |   111111111    |    £20.00
25/09/11  |   222222222    |    £30.00
11/11/11  |   111111111    |    £40.00
01/04/11  |   333333333    |    £10.00
31/03/11  |   333333333    |    £33.00
20/03/11  |   111111111    |    £667.00
21/11/11  |   222222222    |    £564.00
``````

I am trying to find a way of summarising the content in the following way;

``````Reference : 111111111    Total:  £727
``````

So far the only way I have been able to achieve this is to filter the list by each reference number (manually) and then add a simple SUM formula to the bottom of the list of amounts.

Are there any tricks that anyone knows that may speed this up?

What I am trying to achieve is a spreadsheet that highlights each reference number that collectively exceeds over £2,000.

-
Do you know the `VLOOKUP` formula? Not an Excel pro, but shouldn't that work somehow? –  slhck Nov 21 '11 at 16:41
I know how to do VLOOKUP's but I'm not sure how I could utilise it in this instance. –  dannymcc Nov 21 '11 at 16:44
Pivot tables do that kind of thing - basically creates a view of a range from your existing data, overlaying part of the worksheet (a bit like a graph), but showing a table of your data sorted by some columns, subtotalled etc. I can't give a proper answer because I don't have Excel, but from what I remember, it was pretty easy back in the Office 2003 days. –  Steve314 Nov 21 '11 at 17:01

A pivot table will let you organise amounts summed by the reference number. Then you can apply a value filter to only show the totals above 2000.

-

SUMIFS

Here is an example formula: