SOLVED

Count unique text value with specific text criteria (For excel 2016 version)

%3CLINGO-SUB%20id%3D%22lingo-sub-2355055%22%20slang%3D%22en-US%22%3ECount%20unique%20text%20value%20with%20specific%20text%20criteria%20(For%20excel%202016%20version)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2355055%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20All%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20case%20to%20solve%20the%20total%20no.%20of%20food%20type%20by%20respective%20person%20as%20shown%20below%2C%20find%20out%20the%20total%20no.%20of%20food%20type%20owned%20by%20%22Tom%22%20%26amp%3B%20%22Jane%22%20without%20duplicating%20the%20food%20name.%3C%2FP%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%22%E8%9E%A2%E5%B9%95%E6%88%AA%E5%9C%96%202021-05-14%20%E4%B8%8B%E5%8D%8810.40.29.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F280747iF5F1964753AD4815%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22%E8%9E%A2%E5%B9%95%E6%88%AA%E5%9C%96%202021-05-14%20%E4%B8%8B%E5%8D%8810.40.29.png%22%20alt%3D%22%E8%9E%A2%E5%B9%95%E6%88%AA%E5%9C%96%202021-05-14%20%E4%B8%8B%E5%8D%8810.40.29.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%E2%80%83%3C%2FP%3E%3CP%3EI%20have%20tried%20several%20combined%20functions%20like%20sumif%2C%20countifs%2C%20sumproduct%2C%20but%20all%20doesn't%20work%2C%20May%20I%20know%20if%20there's%20any%20way%20to%20achieve%20the%20outcome%20with%20a%20single%20formula%20(without%20using%20pivot%20table).%20Many%20thanks!!!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2355055%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2355144%22%20slang%3D%22en-US%22%3ERe%3A%20Count%20unique%20text%20value%20with%20specific%20text%20criteria%20(For%20excel%202016%20version)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2355144%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1053979%22%20target%3D%22_blank%22%3E%40Diem_Carpe%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20D2%20confirmed%20with%20Ctrl%2BShift%2BEnter%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DSUM(IF(%24A%242%3A%24A%2416%3DC2%2C1%2FCOUNTIFS(%24A%242%3A%24A%2416%2CC2%2C%24B%242%3A%24B%2416%2C%24B%242%3A%24B%2416)))%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFill%20down.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2355236%22%20slang%3D%22en-US%22%3ERe%3A%20Count%20unique%20text%20value%20with%20specific%20text%20criteria%20(For%20excel%202016%20version)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2355236%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1053979%22%20target%3D%22_blank%22%3E%40Diem_Carpe%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20%3CSTRONG%3Emust%3C%2FSTRONG%3E%20confirm%20the%20formula%20with%20%3CSTRONG%3ECtrl%2BShift%2BEnter%3C%2FSTRONG%3E%2C%20otherwise%20it%20won't%20work%20as%20intended.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2356156%22%20slang%3D%22en-US%22%3ERe%3A%20Count%20unique%20text%20value%20with%20specific%20text%20criteria%20(For%20excel%202016%20version)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2356156%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1053979%22%20target%3D%22_blank%22%3E%40Diem_Carpe%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20of%20no%20use%20whatsoever%20for%20the%20OP%20but%20others%20may%20be%20interested%20in%20the%20changes%20that%20Excel%20365%20is%20bringing%20to%20spreadsheets.%26nbsp%3B%20First%20a%20traditional%20nested%20form%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3D%20COUNTA(%20UNIQUE(%20FILTER(Food%2C%20Name%3D%40DistinctName)%20)%20)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E.%26nbsp%3B%20A%20sequential%20alternative%20is%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3D%20LET(%0A%20%20%20%20SelectedFood%2C%20FILTER(Food%2C%20Name%3D%40DistinctName)%2C%0A%20%20%20%20DistinctFood%2C%20UNIQUE(SelectedFood)%2C%0A%20%20%20%20COUNTA(DistinctFood)%20)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3Bwhich%20is%20more%20verbose%20but%20can%20be%20read%20without%20reference%20to%20the%20spreadsheet%20itself.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2359658%22%20slang%3D%22en-US%22%3ERe%3A%20Count%20unique%20text%20value%20with%20specific%20text%20criteria%20(For%20excel%202016%20version)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2359658%22%20slang%3D%22en-US%22%3E%3CP%3E%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%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMany%20thanks%20for%20your%20help!!!%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2363109%22%20slang%3D%22en-US%22%3ERe%3A%20Count%20unique%20text%20value%20with%20specific%20text%20criteria%20(For%20excel%202016%20version)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2363109%22%20slang%3D%22en-US%22%3E%3CP%3E%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%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F214174%22%20target%3D%22_blank%22%3E%40Peter%20Bartholomew%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFurther%20to%20my%20question%2C%20May%20I%20know%20if%20there's%20any%20way%20to%20modify%20the%20function%20so%20that%20it%20allows%20the%20formula%20to%20count%20the%20blank%20cell%20as%20well%20or%20ignore%20the%20rows%20which%20cannot%20match%20column%20A%20and%20Column%20B.%3C%2FP%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%22Diem_Carpe_0-1621320273181.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F281445iC53D70617CF6C3A5%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Diem_Carpe_0-1621320273181.png%22%20alt%3D%22Diem_Carpe_0-1621320273181.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2370574%22%20slang%3D%22en-US%22%3ERe%3A%20Count%20unique%20text%20value%20with%20specific%20text%20criteria%20(For%20excel%202016%20version)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2370574%22%20slang%3D%22en-US%22%3ESorry%20for%20the%20misleading%20description.%20the%20outcome%20shall%20skip%20counting%20the%20blank%20cell%2C%20e.g.%20Jane%20would%20be%202%20(%22Apple%22%20%26amp%3B%20%22Melon%22%2C%20but%20an%20error%20%22%23DIV%2F0!%22%20was%20shown%20when%20i%20use%20the%20same%20formula.%3C%2FLINGO-BODY%3E
Occasional Contributor

Hello All, 

 

I have a case to solve the total no. of food type by respective person as shown below, find out the total no. of food type owned by "Tom" & "Jane" without duplicating the food name.

 

螢幕截圖 2021-05-14 下午10.40.29.png

I have tried several combined functions like sumif, countifs, sumproduct, but all doesn't work, May I know if there's any way to achieve the outcome with a single formula (without using pivot table). Many thanks!!!

10 Replies
best response confirmed by Diem_Carpe (Occasional Contributor)
Solution

@Diem_Carpe 

In D2 confirmed with Ctrl+Shift+Enter:

 

=SUM(IF($A$2:$A$16=C2,1/COUNTIFS($A$2:$A$16,C2,$B$2:$B$16,$B$2:$B$16)))

 

Fill down.

@Hans Vogelaar 

 

I have tried the formula in the excel, and the answer returns "1", is there anything i missed?

螢幕截圖 2021-05-14 下午11.18.26.png

@Diem_Carpe 

You must confirm the formula with Ctrl+Shift+Enter, otherwise it won't work as intended.

@Diem_Carpe 

This is of no use whatsoever for the OP but others may be interested in the changes that Excel 365 is bringing to spreadsheets.  First a traditional nested form

= COUNTA( UNIQUE( FILTER(Food, Name=@DistinctName) ) )

.  A sequential alternative is  

= LET(
    SelectedFood, FILTER(Food, Name=@DistinctName),
    DistinctFood, UNIQUE(SelectedFood),
    COUNTA(DistinctFood) )

 which is more verbose but can be read without reference to the spreadsheet itself.

@Hans Vogelaar @Peter Bartholomew 

 

Further to my question, May I know if there's any way to modify the function so that it allows the formula to count the blank cell as well or ignore the rows which cannot match column A and Column B.

 

Diem_Carpe_0-1621320273181.png

 

@Diem_Carpe 

You write "it allows the formula to count the blank cell". Do you mean that blank counts as a food type? So for example, the count for Jane would be 3 ("Apple", "Melon", blank)?

Sorry for the misleading description. the outcome shall skip counting the blank cell, e.g. Jane would be 2 ("Apple" & "Melon", but an error "#DIV/0!" was shown when i use the same formula.

@Diem_Carpe 

In D2 confirmed with Ctrl+Shift+Enter:

 

=SUM(IF(($A$2:$A$16=C2)*($B$2:$B$16<>""),1/COUNTIFS($A$2:$A$16,C2,$B$2:$B$16,$B$2:$B$16)))

 

See the attached version.