Unique values based on multiple criteria

%3CLINGO-SUB%20id%3D%22lingo-sub-1547337%22%20slang%3D%22en-US%22%3EUnique%20values%20based%20on%20multiple%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1547337%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20list%20of%20members%20with%20duplicate%20entries%20(different%20plots%20on%20an%20allotment).%20I%20need%20to%20find%20out%20how%20many%20unique%20members%20don't%20have%20an%20email%20address%20so%20that%20I%20know%20how%20much%20postage%20I%20have%20to%20buy%20to%20send%20them%20our%20newsletter.%20How%20do%20I%20use%20Unique%20to%20get%20the%20result%3F%20thanks%20in%20advance.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1547337%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1547567%22%20slang%3D%22en-US%22%3ERe%3A%20Unique%20values%20based%20on%20multiple%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1547567%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F740756%22%20target%3D%22_blank%22%3E%40mtalbot22%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETry%20this%20formula%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DCOUNTA(UNIQUE(FILTER(B2%3AB12%2C%20D2%3AD12%3D%22%22)))%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1549150%22%20slang%3D%22en-US%22%3ERe%3A%20Unique%20values%20based%20on%20multiple%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1549150%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F740756%22%20target%3D%22_blank%22%3E%40mtalbot22%3C%2FA%3E%26nbsp%3B%2C%20I%20added%20the%20last%20name%20column%20to%20the%20formula%20inspired%20by%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3A%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DROWS(UNIQUE(FILTER(%24B%242%3A%24C%2412%2CISBLANK(%24D%242%3A%24D%2412))))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

I have a list of members with duplicate entries (different plots on an allotment). I need to find out how many unique members don't have an email address so that I know how much postage I have to buy to send them our newsletter. How do I use Unique to get the result? thanks in advance.

2 Replies

@mtalbot22 

Try this formula:

 

=COUNTA(UNIQUE(FILTER(B2:B12, D2:D12="")))

@mtalbot22 , I added the last name column to the formula inspired by @Hans Vogelaar :

=ROWS(UNIQUE(FILTER($B$2:$C$12,ISBLANK($D$2:$D$12))))