| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Copy rows of one sheet into mutiple sheets based on column value | Excel Discussion (Misc queries) | |||
| Conditional Format as a MACRO | Excel Worksheet Functions | |||
| Help with copying dynamic column selected based on remote cell value and dynamic formula fill | Charts and Charting in Excel | |||
| Return SEARCHED Column Number of Numeric Label and Value | Excel Worksheet Functions | |||
| creating a bar graph | Excel Discussion (Misc queries) | |||
Linear Mode
