Sum one column based on the unique value in another column
 

I have the following columns:

Account# - # of Account Holders - # per State
12345 - 3 - 2 in VA
12345 - 3 - 1 in MD
23456 - 1 - 1 in PA


In this example, I need to show that there are 2 Total Accounts with 4 total
account holders

I have used the following to show the total of unique account numbers:
=SUM(IF(FREQUENCY(MATCH(Data!D2:D91,Data!D2:D91,0) ,MATCH(Data!D2:D91,Data!D2:D91,0))0,1))

How do I SUM Column B based on unique values in Column A?

Thank you much!
Renee

Sum one column based on the unique value in another column
 

I would be tempted to make a pivot table with Accounts in the Row area,
Holders in the Column area and Holders COUNTED in the data area.
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Renee" wrote in message
...
I have the following columns:

Account# - # of Account Holders - # per State
12345 - 3 - 2 in VA
12345 - 3 - 1 in MD
23456 - 1 - 1 in PA


In this example, I need to show that there are 2 Total Accounts with 4
total
account holders

I have used the following to show the total of unique account numbers:
=SUM(IF(FREQUENCY(MATCH(Data!D2:D91,Data!D2:D91,0) ,MATCH(Data!D2:D91,Data!D2:D91,0))0,1))

How do I SUM Column B based on unique values in Column A?

Thank you much!
Renee

Sum one column based on the unique value in another column
 

=SUMPRODUCT(1/COUNTIF(A2:A91,A2:A91&""),B2:B91)


"Renee" wrote:

I have the following columns:

Account# - # of Account Holders - # per State
12345 - 3 - 2 in VA
12345 - 3 - 1 in MD
23456 - 1 - 1 in PA


In this example, I need to show that there are 2 Total Accounts with 4 total
account holders

I have used the following to show the total of unique account numbers:
=SUM(IF(FREQUENCY(MATCH(Data!D2:D91,Data!D2:D91,0) ,MATCH(Data!D2:D91,Data!D2:D91,0))0,1))

How do I SUM Column B based on unique values in Column A?

Thank you much!
Renee