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

Question

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.

Solution 1

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.

Solution 2

SUMIFS

Here is an example formula:

And the answer is correct:

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.

Comments

[dannymcc]

I know how to do VLOOKUP's but I'm not sure how I could utilise it in this instance.

[Admin]

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.

[dannymcc]

Is there a way of doing this autonomously though? As in; highlight any reference that's value exceeds £2,000?

[JMax]

yes, you can use conditional formatting

[Rob]

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]

updated original answer with details on highlighting