5

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.

3
  • Do you know the VLOOKUP formula? Not an Excel pro, but shouldn't that work somehow?
    – slhck
    Nov 21, 2011 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, 2011 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.
    – user31438
    Nov 21, 2011 at 17:01

2 Answers 2

2

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

7

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.

4
  • Is there a way of doing this autonomously though? As in; highlight any reference that's value exceeds £2,000?
    – dannymcc
    Nov 21, 2011 at 17:06
  • 1
    yes, you can use conditional formatting
    – JMax
    Nov 21, 2011 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, 2011 at 17:28
  • updated original answer with details on highlighting
    – Rob
    Nov 21, 2011 at 21:02

You must log in to answer this question.

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