Forum Discussion

Copper Contributor

Aug 22, 2020

Solved

Formula to Count unique non-blank street addresses combined from two columns

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...

ExcelExciting
Aug 25, 2020
Hi 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
MStech.GayleComer.xlsx11 KB

MVP

Aug 23, 2020

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

Copper Contributor

Aug 23, 2020

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"

ExcelExciting
MVP
Aug 25, 2020
Hi 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
MStech.GayleComer.xlsx11 KB
- GayleComer
  Copper Contributor
  Aug 27, 2020
  ExcelExciting
  Thank you very much. I had been searching and searching and trying and trying different ideas. But is is so simple.
  Gayle

Resources