sum with the same criteria

  • Thread starter Thread starter weejeow
  • Start date Start date
W

weejeow

Hie all,

i got a problem with summing. I do not know if it will work in excel
below are the sample: ("customer" is in column A
and "payment" is in column B):


customer | Payment
-----------------------------------
george | 20.00
john | 15.00
micheal | 25.00
john | 10.00
john | 5.00
george | 25.00
micheal | 10.00


Say that if a customer made multiple payment in a day, we need t
calculate the total payment. In this example, george have already pai
45.00, john paid 30.00 and so on. How do i automate this calculation?
I am actually a database guy and have no problem solving this usin
SQL:

select distinct customer,sum(payment) from table_name group b
customer

I need the final result looks something like this:

customer | Payment
-----------------------------------
george | 45.00
john | 30.00
micheal | 35.00

Any idea
 
Have you looked into using pivot tables?

Put the customer in the left column and paymetn in the data area an
select the sum function (that should be the default fucntion anyway).

No macro required, however you may want a macro like

Private Sub Worksheet_Activate()
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
End Sub

to automatically update the pivot table each time you activate th
worksheet. (Put the code in the worksheet module of the sheet tha
contains the pivot table
 
Hie Kieran,
Thanks for your reply. I have tried using pivot with your advise bu
what i get is just "customer" and total numbers of payment:

customer | Payment
-----------------------------------
george | 2
john | 3
micheal | 2

it did not sum up the payment. Any recommendation
 
Doug,

I understand that sumif will do the job

=SUMIF(A:A,john,B:B)

but the problem is that i have a database of thousands of customer
There is no way i can add each name on the formula.

does it rings the bell?

thanks for your advise

Cheers,
Darre
 
Hie guys,

thanks for your help, i manage to get the the sum of each "customer"

i am facing another problem where by if the payment from a customer
0, how do i remove it? eg:

customer | Payment
-----------------------------------
george | 0.00
john | 15.00
micheal | 0.00
david | 20.00

i need something like this:

customer | Payment
-----------------------------------
john | 15.00
david | 20.00

how do i filter it out? automatically without using "autofilter"?

Cheers mate
 
Hi

Double click on the grey heading of your pivot table that
is called "count of amount" and pick "sum" from the list.
That should do the trick.

Hans
 
weejeow said:
I am actually a database guy and have no problem solving this using
SQL
Click to expand...

You can query a (closed) Excel workbook as a Jet data source using sql via ADO.

--
 
Back
Top