# 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:

Of course, you will need to adjust the ranges and criteria to fit your needs.

I was able to get the "highlight ref numbers over X amount "collectively"" to work for ONE reference number using the Conditional formatting tool.

Short of making a Conditional formatting rule for each reference number, I don't know that you can get any better than this using straight formulas. You may need to look into Excel VBA Macros.

-
Is there a way of doing this autonomously though? As in; highlight any reference that's value exceeds £2,000? – dannymcc Nov 21 '11 at 17:06
yes, you can use conditional formatting – JMax Nov 21 '11 at 17:27
I was just looking at conditional formatting. I'll have to poke around with it a little. The whole "collectively" thing kicks this up a bit. – Rob Nov 21 '11 at 17:28
updated original answer with details on highlighting – Rob Nov 21 '11 at 21:02