Excel VBA Code Instead of SumIf Array Formula for Unique Values

Hello Forum,

I have a set of data that is approximately 50,000 rows and need to do a summing function of the number of distinct values in one column that share a common value in another column. I've used the array formula {=SUM(IF(FREQUENCY(IF($B$2:$B$43134=B2,$A$2:$A$43134),$A$2:$A$43134)>0,1))} in the past and while it works it is EXTREMELY slow on our company network and my little HP laptop that could...takes hours and I literally need to leave it running overnight on the docking station and pray there isn't a forced reboot going on that night! I also have to do a similar sum if logic for two other columns. The results feed a model that I use pivot tables to determine percentages eligible, totals, etc.

So I'm looking for VBA code that will loop through the data rows much faster than using the above type array formula or VBA that uses formula logic. I'd like to be able to use a macro button to control when the event occurs, but would like the code to run through every row and provide a result for each. I saw this link for count-ifs, but couldn't make it work for my situation and I'm hoping for similar lightning speed!

http://www.excelforum.com/excel-prog...-countifs.html

I've attached a much more simplified sample workbook that hopefully shows what I'm trying to accomplish, but essentially values in column A all tie to a value in column B. The relationship could be 1:1 or 1:many, basically it's the number of products (column A) on an account (column B). So for each value in column B, sum the total number of unique values in column A and put the result in column C. So for my sample workbook account 8888 has 3 unique values, 9797 has 2, and 1111 has 1. I tried to record a macro and it works one at a time, but would image even if I got it to work on the entire range with a single click of a command button it may still be very slow.

UnqVals.PNG

As always appreciate any help and thank you in advance!

Best regards,
Chris