Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
    
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
up vote 2 down vote accepted

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.

enter image description here

share|improve this answer

SUMIFS

Here is an example formula:

formula

And the answer is correct:

data

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.

newdata

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.

share|improve this answer
    
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
1  
yes, you can use conditional formatting – JMax Nov 21 '11 at 17:27
1  
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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .