Count unique values in range

%3CLINGO-SUB%20id%3D%22lingo-sub-2586782%22%20slang%3D%22en-US%22%3ECount%20unique%20values%20in%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2586782%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EHi%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EI%20want%20to%20work%20out%20how%20many%20unique%20ingredients%20are%20in%20the%20Vegan%20restaurant%2C%20which%20are%20not%20in%20the%20Main%20Restaurant.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EFor%20instance%20here%20in%20recipe%201%20-%20there%20is%201%20unique%20ingredient%20(tofu%20-%20as%20'avocado'%20was%20already%20used%20in%20recipe%201%20in%20the%20Main%20Restaurant).%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EThe%20issue%20with%20my%20formula%20now%20(see%20cell%20'Count%20Unique%20Formula')%20is%20that%20avocado%20isn't%20being%20counted%20when%20it%20is%20compared%20to%20Main%20Restaurant%20in%20Recipe%201%20range%2C%20but%20it%20is%20still%20being%20counted%20when%20compared%20to%20the%20Recipe%202.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2586782%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2586822%22%20slang%3D%22en-US%22%3ERe%3A%20Count%20unique%20values%20in%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2586822%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1112679%22%20target%3D%22_blank%22%3E%40arianegr%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHow%20about%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DSUMPRODUCT((C%242%3AC%244%26lt%3B%26gt%3B%22%22)*ISERROR(MATCH(C%242%3AC%244%2CC%245%3AC%247%2C0)))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Ein%20C10.%20Fill%20to%20the%20right%20to%20D10.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2586929%22%20slang%3D%22en-US%22%3ERe%3A%20Count%20unique%20values%20in%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2586929%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1112679%22%20target%3D%22_blank%22%3E%40arianegr%3C%2FA%3E%26nbsp%3B%20I%20have%20a%20coupe%20of%20questions.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3EWhy%20can't%20you%20use%20the%20list%20you%20have%20in%20column%20B%3F%3C%2FP%3E%3CP%3EAre%20you%20interested%20in%20the%20TOTAL%20across%20all%20recipes%20or%20the%20%23%20unique%20ingredients%20in%20each%20recipe%3F%3C%2FP%3E%3CP%3EIf%20you%20are%20interested%20in%20individual%20recipes%20is%20it%20if%20the%20vegan%20recipe%20has%20a%20unique%20ingredient%20compared%20to%20the%20comparable%20regular%20recipe%20or%20across%20all%20other%20recipes%3F%3C%2FP%3E%3CP%3EHere%20is%20equation%20for%20each%20individual%20recipe%20vs%20whole%20regular%20menu%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DSUMPRODUCT(--((COUNTIFS(%24C%245%3A%24D%247%2CC%242%3AC%244)%2BISBLANK(C%242%3AC%244))%3D0))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3Eand%20here%20is%20whole%20vegan%20menu%20vs%20whole%20regular%20menu%20(again%20without%20using%20Col%20B)%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DSUMPRODUCT(((COUNTIFS(C5%3AE7%2CC2%3AE4)%2BISBLANK(C2%3AE4))%3D0)%2F(COUNTIFS(C2%3AE4%2CC2%3AE4)%2BISBLANK(C2%3AE4)))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi
I want to work out how many unique ingredients are in the Vegan restaurant, which are not in the Main Restaurant.
For instance here in recipe 1 - there is 1 unique ingredient (tofu - as 'avocado' was already used in recipe 1 in the Main Restaurant).
The issue with my formula now (see cell 'Count Unique Formula') is that avocado isn't being counted when it is compared to Main Restaurant in Recipe 1 range, but it is still being counted when compared to the Recipe 2.

4 Replies

@arianegr 

How about

=SUMPRODUCT((C$2:C$4<>"")*ISERROR(MATCH(C$2:C$4,C$5:C$7,0)))

in C10. Fill to the right to D10.

@arianegr  I have a coupe of questions.  

Why can't you use the list you have in column B?

Are you interested in the TOTAL across all recipes or the # unique ingredients in each recipe?

If you are interested in individual recipes is it if the vegan recipe has a unique ingredient compared to the comparable regular recipe or across all other recipes?

Here is equation for each individual recipe vs whole regular menu:

=SUMPRODUCT(--((COUNTIFS($C$5:$D$7,C$2:C$4)+ISBLANK(C$2:C$4))=0))

and here is whole vegan menu vs whole regular menu (again without using Col B)

=SUMPRODUCT(((COUNTIFS(C5:E7,C2:E4)+ISBLANK(C2:E4))=0)/(COUNTIFS(C2:E4,C2:E4)+ISBLANK(C2:E4)))

 

Hi @mtarler ,

Thank you very much for your input.

I used the first equation. When testing it by adding a third recipe - I found that it seems to double count tofu.

For clarification, I want to count the incremental unique amount of vegan ingredients which are NOT used in the main restaurant. 

In the file attached, seems like it double counts tofu if it's being used in another vegan recipe - see file attached.

I think we are nearly there!

@arianegr  Ah, you want it to be incremental, that is the part I didn't get.  That said you needed to make a variation of the second formula that was for the total.  I bet there is an 'easier' version but this is what I came up with:

=SUMPRODUCT(((COUNTIFS($C$6:$E$8,$C$2:C5)+ISBLANK($C$2:C5))=0)/(COUNTIFS($C$2:C5,$C$2:C5)+ISBLANK($C$2:C5)))