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 excel 2010 and I am trying to do the following (the table below has been done manually)

Customer       Order type        Revenue         Sum
001            Pears             50              
001            Plums             70        
001            Strawberries      10              130
002            Pears             10
002            Strawberries      20              30
003            Pears             30
003            Plums             10
003            Strawberries      10              50

Basically I have data from the first 3 columns and I want to have an aggregate value in the fourth column for every customer: that is sum all the revenue from customer xxx

This should have been done by the BI too, I use, however I have not enough privileges to work this out with the appropriate sql queries

I have to do this for thousands of records, any idea about which function can make it? This is an interesting topic for anyone dealing with revenue reports.

Thanks

RESOLUTION: I tried the formula suggested by Santosh, it works pretty well. You get the sum only once, then you just do some basic manipulations and you can get one row woth the total revenue per customer which is what I was looking for.

Thank you!

share|improve this question

migrated from stackoverflow.com Mar 30 '13 at 7:28

This question came from our site for professional and enthusiast programmers.

    
Either Pivot table or SUMIF() can do this –  Tim Williams Mar 29 '13 at 18:00

5 Answers 5

You can use the SUMIF function. =IF(A2=A3,"",SUMIF(A:A,A2,C:C)) drag it.

enter image description here

share|improve this answer
    
I tested this formula; it gives the right answer, but it displays it on every row. –  James Jenkins Mar 29 '13 at 18:11
    
@JamesJenkins have updated it. –  Santosh Mar 29 '13 at 18:14
    
That works better :) –  James Jenkins Mar 29 '13 at 18:17
    
+ 1 Another good one –  Siddharth Rout Mar 29 '13 at 18:30
    
@Santosh tried your formula, works pretty well, it actually displays the sum once, then with some copy&paste manipulations I obtained the results I wanted –  cpiras Mar 29 '13 at 21:05

Try this formula in D2 filled down

=IF(A3=A2,"",SUM(C$2:C2)-SUM(D$1:D1))

see here

share|improve this answer
    
I tested this formula; It gives very wrong answers. -40, -150, & -200 but it does only display it at the end of the customer number. –  James Jenkins Mar 29 '13 at 18:13
3  
+ 1 Barry and wrong formula?... That would be a first :) –  Siddharth Rout Mar 29 '13 at 18:26
    
Thanks Sid! - I tested the formula and it works assuming the data is laid out as per Santosh's picture - I'm trying to attach a sample but having problems at the moment......... –  barry houdini Mar 29 '13 at 20:43
    
I added a link to a sample file containing the suggested formula, it seems to return the required values...... –  barry houdini Mar 29 '13 at 20:59
    
I pasted it twice, I must have killed it somehow. Ah ha, I see the problem, I pasted it into column F, and it is dependent on being in column D. Sorry my error –  James Jenkins Mar 30 '13 at 10:10

This can be done with two formulas, where one column is hidden.

In D2, running sum of the values

=IF(A2=A1,C2+D1,C2)

In E2, display on the last values

=IF(A2=A3,"",D2)

Hide column D, so the running sum is not displayed

Sum

share|improve this answer

Consider using a pivot table. This will give you a unique list of customers and sum up the values in the Revenue field.

share|improve this answer

Use of Subtotal would seem easiest.

share|improve this answer

Your Answer

 
discard

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