# Get unique values from column and get Counts and Sums based on these values

1. ## Get unique values from column and get Counts and Sums based on these values

I had a worksheet (please see attached) with 2 columns -- column A is list of "volunteers" and column B is corresponding donations solicited. I need to get a list all volunteers, do a count of how many donations each one solicited, and the total amount each one solicited.

Following is my code:
``Please Login or Register  to view this content.``
This macro doesn't work in real life situation, where I would not know in advance how many "unique" volunteers there are. The formulae in the macro are totally useless since hardcoding the criteria (volunteer initials) would not be possible.

Shall very much appreciate if some one can give help me out.

Thank you very much
Henry

2. ## Re: Get unique values from column and get Counts and Sums based on these values

No vba needed, no formulas needed.

Welcome to the fun, and ultimately simple, world of Pivot Tables: http://screencast.com/t/zsmVeWzy1b

3. ## Re: Get unique values from column and get Counts and Sums based on these values

Based on your sample file, you dont need VBA for this.

in D6...
=IFERROR(INDEX(\$A\$2:\$A\$102,MATCH(0,INDEX(COUNTIF(\$D\$5:D5,\$A\$2:\$A\$102),0,0),0)),"")
for the count E6...
=COUNTIF(\$A\$2:\$A\$102,D6)
For the sum F6...
=SUMIF(\$A\$2:\$A\$102,D6,\$B\$2:\$B\$103)
all copied down

4. ## Re: Get unique values from column and get Counts and Sums based on these values

Hi Dear

Thanks very much for your prompt reply. My problem is Columns A & B are actually extracted from a much bigger worksheet of raw data using a macro. I am hoping to use one single macro to complete the whole process. That is why the solution did not work for me.

Also FYI total number of donations for any one period of time varies (i.e. variable no of rows), and the number of unique volunteers varies too (could be 4 this period, 14 next period, 9 next period, and different set of people every period too).

I don't think I have the skill to write this part of the macro.

Thanks again!
Henry

5. ## Re: Get unique values from column and get Counts and Sums based on these values

If you extend the range in my suggestion to cover your largest range, it will auto-populate, no matter how many volunteers you have. The PT suggested will also do that too, but will need to be updated when new data is added. From the sounds of it, you did not even try them, I suggest you give them both a try and then see what you think

6. ## Re: Get unique values from column and get Counts and Sums based on these values

Hi Ford

Your formulae work perfectly with the range extended to entire column. Thanks.

My goal, however, is still to figure a way to put your solution into a macro so I can process my raw data with just one hit of a button.

Many thanks again.
Henry

7. ## Re: Get unique values from column and get Counts and Sums based on these values

Which is why you should use the Pivot Table, it would update itself for whatever your data would be in the original columns. My two cents.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1