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

[GayleComer]

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

[#2]

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

[#3]

@Faraz Shaikh 

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"

[#4 · Accepted solution]

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

[#5]

@Faraz Shaikh 

Thank you very much. I had been searching and searching and trying and trying different ideas. But is is so simple.

Gayle

[#6]

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