to sum or not to sum, cute huh?

[mrxwantobe]

So I have column B with some membership numbers. There are some dups. for example 110 is listed 8 times. In column C is some dollar amounts I need to total. Will sumif add the dollar totals in C if the member number in column B matches?

 

[MrKowz]

Yes:

=SUMIF(B:B,110,C:C)

 

[psrs0810]

why don't you subtotal?
For every change in column B, subtotal column C

 

[CWatts]

Yes it will, however you'll still be left with a list of duplicates.

I'd reccommend using a pivot table.

Make sure column A and B are lableled, I'll assum "Member" and "Dollars".

Highlight your data and insert a pivot table. I dont know what version of excel you're using so you may have to find it.

Drag "member" to the "Row" section of the pivot table, and then drag "Dollars" to the "Value" section. Repeat this so that it is entered twice.

You'll then have two "Sum of Dollars". Click on the firstone, choose Value Field Settings and change "Sum" to "Count".

You can then easily at a glance see total dollar value for each member, as well as the number of entries for each member.

 

[mrxwantobe]

The pivot table sounds like what I want. I'm confused about two things. I don't see the Value section and I don't get the drag twice. Can you help?

 

[CWatts]

Sure - what version of excel do you have?

Also, check out the podcasts. They've been a great help for me when I was looking for cool tips and tricks. There's a few on Pivot Tables that may help if you're more of a visual person.

http://www.mrexcel.com/excel_video_training.html

 

[mrxwantobe]

Excel 2002 XP and thanks so much for this help. this is wonderful.

 

[CWatts]

For 2002 and 2003 (i believe they're identical) you'll have a wizard that looks like the image below.and a field list like the one on the right which will have "members" and "dollars" depending on what you titled your columns.

Drag "Members" to the "Row" box and drag "dollars" to the "Data" box. From there, you either double click or right click on the first dollars to change it from "Sum" to Count.

I havent used 2002 or 2003 for a while so my memory may be rusty, but the podcasts can hopufully fill in blanks I may have missed.

 

[mrxwantobe]

I'm closer. I got the pivot table to tell me there are two entrys in A for member 17 and 15 entries for member 110 and so on, so it's totaling column A not be where the dollars are

 

[mrxwantobe]

OMG, I got it. thanks so much. I will go do something nice for someone in my field of expertise.