sum with the same criteria

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
 
K

Kieran

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
 
W

weejeow

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
 
W

weejeow

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
 
W

weejeow

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
 
H

Hans

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
 
O

onedaywhen

weejeow said:
I am actually a database guy and have no problem solving this using
SQL

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

--
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top