Aug 22 2020 12:22 PM
I have a list of street addresses with the street address(number street, city, zip) in column B and the apartment number in column C. Some of the addresses (B & C together) are duplicates.
I need to insert a formula at the end of the list to count the number of unique addresses (that is B and C combined) in the list.
I am new to excel and need help.
Thank you,
Gayle
Aug 22 2020 09:35 PM
Hi @GayleComer
You can use the UNIQUE formula to get you result.
if you are still facing trouble please attach the sample file with your desired result entered manually
Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert
If you find the above solution resolved your query don't forget mark as Official/Best Answer to help the other members find it more
Aug 23 2020 11:44 AM
Thank you for your response. Here is the sample spreadsheet:
Name | Address | Apt | Other | |||
John Doe | 388 N Federal Hwy, Deerfield Beach, FL 33441 | 101 | ||||
388 N Federal Hwy, Deerfield Beach, FL 33441 | 102 | |||||
John Doe | 388 N Federal Hwy, Deerfield Beach, FL 33441 | 103 | ||||
Jane Doe | 388 N Federal Hwy, Deerfield Beach, FL 33441 | 103 | ||||
Baby Doe | 388 N Federal Hwy, Deerfield Beach, FL 33441 | 103 | ||||
388 N Federal Hwy, Deerfield Beach, FL 33441 | 104 | |||||
388 N Federal Hwy, Deerfield Beach, FL 33441 | 105 | |||||
388 N Federal Hwy, Deerfield Beach, FL 33441 | 105 | |||||
388 N Federal Hwy, Deerfield Beach, FL 33441 | 106 | |||||
392 N Federal Hwy, Deerfield Beach, FL 33441 | 101 | |||||
392 N Federal Hwy, Deerfield Beach, FL 33441 | 101 | |||||
392 N Federal Hwy, Deerfield Beach, FL 33441 | 102 | |||||
392 N Federal Hwy, Deerfield Beach, FL 33441 | 103 | |||||
392 N Federal Hwy, Deerfield Beach, FL 33441 | 104 | |||||
392 N Federal Hwy, Deerfield Beach, FL 33441 | 104 | |||||
392 N Federal Hwy, Deerfield Beach, FL 33441 | 105 | |||||
392 N Federal Hwy, Deerfield Beach, FL 33441 | 106 | |||||
394 N Federal Hwy, Deerfield Beach, FL 33441 | 204 | |||||
394 N Federal Hwy, Deerfield Beach, FL 33441 | 205 | |||||
394 N Federal Hwy, Deerfield Beach, FL 33441 | 206 | |||||
9 Unique addresses from the formula: (=COUNTA(UNIQUE(C2:C21))) | ||||||
"When the real answer is 15 unique addresses" |
Aug 25 2020 12:49 AM
SolutionHi @GayleComer
thanks for sharing the sample data, construct the formula as below
=COUNTA(UNIQUE($B$2:$B$21&$C$2:$C$21))
Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert
If you find the above solution resolved your query don't forget mark as Official/Best Answer to help the other members find it more
Aug 27 2020 11:38 AM
Thank you very much. I had been searching and searching and trying and trying different ideas. But is is so simple.
Gayle
Aug 27 2020 06:47 PM
Hi @GayleComer
Glad to hear your query is resolved, don't forget to make it as best response that shall help other to find as well
Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert
If you find the above solution resolved your query don't forget mark as Official/Best Answer to help the other members find it more
Aug 25 2020 12:49 AM
SolutionHi @GayleComer
thanks for sharing the sample data, construct the formula as below
=COUNTA(UNIQUE($B$2:$B$21&$C$2:$C$21))
Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert
If you find the above solution resolved your query don't forget mark as Official/Best Answer to help the other members find it more