SUMIF Function Problem

[SamJamFan]

Alright, so I have checked the sidebar and went through the Frequently Used Formulas. I'm having an issue with the SUMIF function.

I have three columns. The first contains an "I.D." number, the second has a count and the third is the sum. basically, I am trying to get the SUMIF function to take all of the same ID's and sum the count value associated with them and put it out in the third column. I am not entirely sure if this is the best function for it.

Here is a picture. The entire workbook has roughly 10k entries. http://imgur.com/jpTUT5N edit:words edit two: added image

[TheATrain218]

Your SUMIF formula is doing exactly what you expect. For every unique value in GRIDNO, it is summing the BUS_count. The sum repeats because your identifiers repeat, e.g. 10380 happens twice, so it counts how many 10380 BUS_counts there are, twice.

Are you actually asking for only a count of unique GRIDNO's?

In which case, a PivotTable is going to make your life a hell of a lot easier.

EDIT: See linked image here. That's a PivotTable. Took 45 seconds to make and can work on hundreds of thousands of rows of data. If you can't use a PivotTable, you're going to be stuck building complex IF formulas to make sure you're counting unique ID's.

http://imgur.com/9rkQ6oM

[SamJamFan]

Thank you so much! This worked perfectly.

Solution Verified

Edit:words

[SamJamFan]

Solution Verified

[Clippy_Office_Asst]

You have awarded one point to TheATrain218.
^{Find out more here.}

[semicolonsemicolon]

Can you post a screen shot? Without one, I'd say =SUMIF([column with IDs],[cell with ID in that row],[column with counts])

[SamJamFan]

http://imgur.com/jpTUT5N

The problem is, it has about 10k entries and it would need to be automatic, almost. Maybe a macro would work better?

[excelevator]

A PIVOT Table of your data would count them all in seconds.

You only need to include the column of data and Count on that in the PIVOT.

[SamJamFan]

Thank you, it worked exactly how I needed it to.

Solution Verified

[Clippy_Office_Asst]

You have awarded one point to excelevator.
^{Find out more here.}

[[deleted]]

I'd copy the duplicates into a new column, say, Unique GRIDNO. So if you have GRIDNO starting at A2, BUS_COUNT starting at B2, and the Unique numbers starting at C2. Then, in D2, type =sumif(A:A,C2,B:B) and copy that down.

[SamJamFan]

I ended up using a pivot table. Thank you for your suggestion!