Forum Discussion

GayleComer's avatar
GayleComer
Copper Contributor
Aug 22, 2020

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's avatar
    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

     

GayleComer's avatar
GayleComer
Copper Contributor

ExcelExciting 

Thank you for your response. Here is the sample spreadsheet:

NameAddressAptOther   
John Doe388 N Federal Hwy, Deerfield Beach, FL 33441101    
 388 N Federal Hwy, Deerfield Beach, FL 33441102    
John Doe388 N Federal Hwy, Deerfield Beach, FL 33441103    
Jane Doe388 N Federal Hwy, Deerfield Beach, FL 33441103    
Baby Doe388 N Federal Hwy, Deerfield Beach, FL 33441103    
 388 N Federal Hwy, Deerfield Beach, FL 33441104    
 388 N Federal Hwy, Deerfield Beach, FL 33441105    
 388 N Federal Hwy, Deerfield Beach, FL 33441105    
 388 N Federal Hwy, Deerfield Beach, FL 33441106    
 392 N Federal Hwy, Deerfield Beach, FL 33441101    
 392 N Federal Hwy, Deerfield Beach, FL 33441101    
 392 N Federal Hwy, Deerfield Beach, FL 33441102    
 392 N Federal Hwy, Deerfield Beach, FL 33441103    
 392 N Federal Hwy, Deerfield Beach, FL 33441104    
 392 N Federal Hwy, Deerfield Beach, FL 33441104    
 392 N Federal Hwy, Deerfield Beach, FL 33441105    
 392 N Federal Hwy, Deerfield Beach, FL 33441106    
 394 N Federal Hwy, Deerfield Beach, FL 33441204    
 394 N Federal Hwy, Deerfield Beach, FL 33441205    
 394 N Federal Hwy, Deerfield Beach, FL 33441206    
 9 Unique addresses from the formula:  (=COUNTA(UNIQUE(C2:C21)))     
 "When the real answer is 15 unique addresses"     
ExcelExciting's avatar
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

 

  • GayleComer's avatar
    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

    • ExcelExciting's avatar
      ExcelExciting
      MVP
      Aug 28, 2020

      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

Resources