SOLVED

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

%3CLINGO-SUB%20id%3D%22lingo-sub-1605813%22%20slang%3D%22en-US%22%3EFormula%20to%20Count%20unique%20non-blank%20street%20addresses%20combined%20from%20two%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1605813%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20list%20of%20street%20addresses%20with%20the%20street%20address(number%20street%2C%20city%2C%20zip)%20in%20column%20B%20and%20the%20apartment%20number%20in%20column%20C.%20Some%20of%20the%20addresses%20(B%20%26amp%3B%20C%20together)%20are%20duplicates.%3C%2FP%3E%3CP%3EI%20need%20to%20insert%20a%20formula%20at%20the%20end%20of%20the%20list%20to%20count%20the%20number%20of%20unique%20addresses%20(that%20is%20B%20and%20C%20combined)%20in%20the%20list.%3C%2FP%3E%3CP%3EI%20am%20new%20to%20excel%20and%20need%20help.%3C%2FP%3E%3CP%3EThank%20you%2C%3C%2FP%3E%3CP%3EGayle%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1606168%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20to%20Count%20unique%20non-blank%20street%20addresses%20combined%20from%20two%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1606168%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F767878%22%20target%3D%22_blank%22%3E%40GayleComer%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20use%20the%20UNIQUE%20formula%20to%20get%20you%20result.%26nbsp%3B%3C%2FP%3E%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Snag_f397600.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F214118i9259466E28F8DA8F%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22Snag_f397600.png%22%20alt%3D%22Snag_f397600.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eif%20you%20are%20still%20facing%20trouble%20please%20attach%20the%20sample%20file%20with%20your%20desired%20result%20entered%20manually%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3ERegards%2C%20Faraz%20Shaikh%20%7C%20MCT%2C%20MIE%2C%20MOS%20Master%2C%20Excel%20Expert%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CEM%3E%3CFONT%20color%3D%22%23808080%22%3EIf%20you%20find%20the%20above%20solution%20resolved%20your%20query%20don't%20forget%20mark%20as%26nbsp%3B%3CSPAN%3EOfficial%2FBest%20Answer%26nbsp%3Bto%20help%20the%20other%20members%20find%20it%20more%3C%2FSPAN%3E%3C%2FFONT%3E%3C%2FEM%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1606848%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20to%20Count%20unique%20non-blank%20street%20addresses%20combined%20from%20two%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1606848%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F156456%22%20target%3D%22_blank%22%3E%40Faraz%20Shaikh%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20response.%20Here%20is%20the%20sample%20spreadsheet%3A%3C%2FP%3E%3CTABLE%20width%3D%22841%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22159%22%3EName%3C%2FTD%3E%3CTD%20width%3D%22316%22%3EAddress%3C%2FTD%3E%3CTD%20width%3D%2264%22%3EApt%3C%2FTD%3E%3CTD%20width%3D%22110%22%3EOther%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EJohn%20Doe%3C%2FTD%3E%3CTD%3E388%20N%20Federal%20Hwy%2C%20Deerfield%20Beach%2C%20FL%2033441%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E101%3C%2FTD%3E%3CTD%20width%3D%22110%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E388%20N%20Federal%20Hwy%2C%20Deerfield%20Beach%2C%20FL%2033441%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E102%3C%2FTD%3E%3CTD%20width%3D%22110%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EJohn%20Doe%3C%2FTD%3E%3CTD%3E388%20N%20Federal%20Hwy%2C%20Deerfield%20Beach%2C%20FL%2033441%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E103%3C%2FTD%3E%3CTD%20width%3D%22110%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EJane%20Doe%3C%2FTD%3E%3CTD%3E388%20N%20Federal%20Hwy%2C%20Deerfield%20Beach%2C%20FL%2033441%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E103%3C%2FTD%3E%3CTD%20width%3D%22110%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EBaby%20Doe%3C%2FTD%3E%3CTD%3E388%20N%20Federal%20Hwy%2C%20Deerfield%20Beach%2C%20FL%2033441%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E103%3C%2FTD%3E%3CTD%20width%3D%22110%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E388%20N%20Federal%20Hwy%2C%20Deerfield%20Beach%2C%20FL%2033441%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E104%3C%2FTD%3E%3CTD%20width%3D%22110%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E388%20N%20Federal%20Hwy%2C%20Deerfield%20Beach%2C%20FL%2033441%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E105%3C%2FTD%3E%3CTD%20width%3D%22110%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E388%20N%20Federal%20Hwy%2C%20Deerfield%20Beach%2C%20FL%2033441%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E105%3C%2FTD%3E%3CTD%20width%3D%22110%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E388%20N%20Federal%20Hwy%2C%20Deerfield%20Beach%2C%20FL%2033441%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E106%3C%2FTD%3E%3CTD%20width%3D%22110%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E392%20N%20Federal%20Hwy%2C%20Deerfield%20Beach%2C%20FL%2033441%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E101%3C%2FTD%3E%3CTD%20width%3D%22110%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E392%20N%20Federal%20Hwy%2C%20Deerfield%20Beach%2C%20FL%2033441%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E101%3C%2FTD%3E%3CTD%20width%3D%22110%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E392%20N%20Federal%20Hwy%2C%20Deerfield%20Beach%2C%20FL%2033441%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E102%3C%2FTD%3E%3CTD%20width%3D%22110%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E392%20N%20Federal%20Hwy%2C%20Deerfield%20Beach%2C%20FL%2033441%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E103%3C%2FTD%3E%3CTD%20width%3D%22110%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E392%20N%20Federal%20Hwy%2C%20Deerfield%20Beach%2C%20FL%2033441%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E104%3C%2FTD%3E%3CTD%20width%3D%22110%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E392%20N%20Federal%20Hwy%2C%20Deerfield%20Beach%2C%20FL%2033441%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E104%3C%2FTD%3E%3CTD%20width%3D%22110%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E392%20N%20Federal%20Hwy%2C%20Deerfield%20Beach%2C%20FL%2033441%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E105%3C%2FTD%3E%3CTD%20width%3D%22110%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E392%20N%20Federal%20Hwy%2C%20Deerfield%20Beach%2C%20FL%2033441%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E106%3C%2FTD%3E%3CTD%20width%3D%22110%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E394%20N%20Federal%20Hwy%2C%20Deerfield%20Beach%2C%20FL%2033441%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E204%3C%2FTD%3E%3CTD%20width%3D%22110%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E394%20N%20Federal%20Hwy%2C%20Deerfield%20Beach%2C%20FL%2033441%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E205%3C%2FTD%3E%3CTD%20width%3D%22110%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E394%20N%20Federal%20Hwy%2C%20Deerfield%20Beach%2C%20FL%2033441%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E206%3C%2FTD%3E%3CTD%20width%3D%22110%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E9%20Unique%20addresses%20from%20the%20formula%3A%26nbsp%3B%20(%3DCOUNTA(UNIQUE(C2%3AC21)))%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%22When%20the%20real%20answer%20is%2015%20unique%20addresses%22%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1609957%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20to%20Count%20unique%20non-blank%20street%20addresses%20combined%20from%20two%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1609957%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F767878%22%20target%3D%22_blank%22%3E%40GayleComer%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethanks%20for%20sharing%20the%20sample%20data%2C%20construct%20the%26nbsp%3Bformula%20as%20below%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DCOUNTA(UNIQUE(%24B%242%3A%24B%2421%26amp%3B%24C%242%3A%24C%2421))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Snag_1a3959ad.png%22%20style%3D%22width%3A%20639px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F214592i4F094C375E123CDD%2Fimage-dimensions%2F639x509%3Fv%3D1.0%22%20width%3D%22639%22%20height%3D%22509%22%20title%3D%22Snag_1a3959ad.png%22%20alt%3D%22Snag_1a3959ad.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%E2%80%83%3C%2FP%3E%3CP%3E%3CSPAN%3ERegards%2C%20Faraz%20Shaikh%20%7C%20MCT%2C%20MIE%2C%20MOS%20Master%2C%20Excel%20Expert%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CEM%3E%3CFONT%20color%3D%22%23808080%22%3EIf%20you%20find%20the%20above%20solution%20resolved%20your%20query%20don't%20forget%20mark%20as%26nbsp%3B%3CSPAN%3EOfficial%2FBest%20Answer%26nbsp%3Bto%20help%20the%20other%20members%20find%20it%20more%3C%2FSPAN%3E%3C%2FFONT%3E%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1617012%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20to%20Count%20unique%20non-blank%20street%20addresses%20combined%20from%20two%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1617012%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F156456%22%20target%3D%22_blank%22%3E%40Faraz%20Shaikh%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20very%20much.%20I%20had%20been%20searching%20and%20searching%20and%20trying%20and%20trying%20different%20ideas.%20But%20is%20is%20so%20simple.%3C%2FP%3E%3CP%3EGayle%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1617672%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20to%20Count%20unique%20non-blank%20street%20addresses%20combined%20from%20two%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1617672%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F767878%22%20target%3D%22_blank%22%3E%40GayleComer%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGlad%20to%20hear%20your%20query%20is%20resolved%2C%20don't%20forget%20to%20make%20it%20as%20best%20response%20that%20shall%20help%20other%20to%20find%20as%20well%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3ERegards%2C%20Faraz%20Shaikh%20%7C%20MCT%2C%20MIE%2C%20MOS%20Master%2C%20Excel%20Expert%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CEM%3E%3CFONT%20color%3D%22%23808080%22%3EIf%20you%20find%20the%20above%20solution%20resolved%20your%20query%20don't%20forget%20mark%20as%26nbsp%3B%3CSPAN%3EOfficial%2FBest%20Answer%26nbsp%3Bto%20help%20the%20other%20members%20find%20it%20more%3C%2FSPAN%3E%3C%2FFONT%3E%3C%2FEM%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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

5 Replies

Hi @GayleComer 

 

You can use the UNIQUE formula to get you result. 

 

Snag_f397600.png

 

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

@Faraz Shaikh 

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"     
Best Response confirmed by GayleComer (New Contributor)
Solution

Hi @GayleComer 

 

thanks for sharing the sample data, construct the formula as below

 

=COUNTA(UNIQUE($B$2:$B$21&$C$2:$C$21))

 

Snag_1a3959ad.png

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

 

@Faraz Shaikh 

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

Gayle

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