Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

2 Answers 2

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.