• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Countifs for Unique Values

D

dkopy

New Member
I'm trying to perform a total count of unique cells based on multiple criteria/conditions. I've outlined a scenario on the spreadsheet linked below, as well as what the outcomes SHOULD be. I just need the formula. I think it should involve the sum(if(frequency(match functions, but I can't seem to get it right for this particular scenario, because I need to input a ">0" condition in there.


Any help is greatly appreciated!!


https://www.dropbox.com/sh/587qyfda8zsq19g/y5W5OL1mU3
 
If you use the top-right search box, you'll find that there are several threads/articles here that talk about doing an unique count with criteria(s). I'd recommend starting here:

http://chandoo.org/forums/topic/count-unique-values-if-multiple-critera-are-met
 
Hi


Welcome to the Forum..


If you search in top Left, You will find a huge range of solution for your question..


Your Provided data is :

[pre]
Code: 
Fruit	Store	Johnson Family	Davis Family	Smith Family
Apple	Safeway	2	4	0
Apple	Ralphs	0	5	0
Orange	Safeway	2	0	1
Orange	Ralphs	2	0	5
Banana	Safeway	0	0	2
Banana	Ralphs	0	2	1
Pear	Safeway	1	1	5
Pear	Ralphs	5	2	1
Pear	Costco	5	4	2
Lemon	Safeway	0	0	5
Strawberry	Safeway	4	0	3
Blueberry	Safeway	0	1	4
Blueberry	Ralphs	5	1	2
Blueberry	Costco	2	2	5

Total UNIQUE Fruit eaten by each family..[/pre]
You can simply use the below Formula, with Ctrl Shift Enter

{=SUM(IF(C$4:C$17>0,1/COUNTIFS(C$4:C$17,">0",$A$4:$A$17,$A$4:$A$17)),0)}


https://dl.dropbox.com/u/78831150/Excel/Unique%20Countifs%20Sample%208.9.12%20dkopy.xlsx


Please let us know if you are facing any issue..
 
Thank you so much Debraj!


I have one additional question. I'm trying to perform this function in a different place, and for dozens of different "families". How would you embed the INDEX function into this formula so that I could call it out on multiple tabs automatically? I provided an example of the way that I'm summing on other tabs (using INDEX) at the bottom of the linked sheet below.


Thanks again!


https://www.dropbox.com/s/fdgk5d6bm8x2wx0/Unique%20Countifs%20Sample%208.9.12.xlsx
 
Hi dkopy,


Sorry, I tried hard but failed... Don't loose hope, Someone will surely get back to us..


But, for the time being, I have used a helper column, and without using Index, I have achieve the target. I also not satisfied with the trick.. :(


https://dl.dropbox.com/u/78831150/Excel/Unique%20Countifs%20Sample%208.9.12%20-%20dkopy.xlsx


Hi F1Racer..


Can someone help us to remove the helper Column from the attached or provide us a Neat Forensic Formula for the Unique Value with non-same-size-Condition.. Details is in the attachment.

[pre]
Code: 
Fruit		Store	Johnson	Davis 	Smith
----------------------------------------------
Apple		Safeway	2	4	0
Apple		Ralphs	0	5	0
Orange		Safeway	2	0	1
Orange		Ralphs	2	0	5
Banana		Safeway	0	0	2
Banana		Ralphs	0	2	1
Pear		Safeway	1	1	5
Pear		Ralphs	5	2	1
Pear		Costco	5	4	2
Lemon		Safeway	0	0	5
Strawberry	Safeway	4	0	3
Blueberry	Safeway	0	1	4
Blueberry	Ralphs	5	1	2
Blueberry	Costco	2	2	5

Unique Fruit Eaten by <Johnson> = "Need Formula"
[/pre]

With the change of the name Formula need to display Unique Fruit Eaten...


Regards,

Deb
 
Hi Debraj ,


I am not clear on where you want a formula which does not use the helper columns you have created.


You have formulae in C19 , D19 and E19 , which do not make use of the helper columns ; you have a formula in C24 , which does make use of the helper columns.


Can you explain with reference to cell addresses ?


Narayan
 
Hi Narayank991,


I need formula in C24, so that if A24 changes, if also reflect the Answer.. Currently I am also providing the same answer but with the help of helper Column. I just need to eliminate this helper Column and find Unique Match according to the criteria..


as per dkopy, he has dozens of different "families", so it is a extra task to use the helper column..


Thanks in advance..


Regards,

Deb
 
Hi Debraj ,


I still don't understand , sorry.


The result in C24 is identical to the results in row 19 ; why does the formula in C24 have to refer to the helper columns , when it can just use the data validation drop-down selection to INDEX into the results in row 19 , which have been obtained without using the helper columns ?


Narayan
 
Sorry Narayan,


Its my fault that I gave you a question with answer sheet and asking do it with otherway..


Can you please download the below file, I need a formula in C22, so that If i change the value in A22 it will reflect the Unique Fruit eaten by that Family..


https://dl.dropbox.com/u/78831150/Excel/Unique%20Countifs%20Sample%208.9.12%20-%20part%202.xlsx


Regards,

Deb
 
Hi Debraj ,


Try this :


=SUM(IF(OFFSET(C4:E17,0,MATCH(A22,C3:E3,0)-1,ROWS(C4:C17),1)>0,1/COUNTIFS(OFFSET(C4:E17,0,MATCH(A22,C3:E3,0)-1,ROWS(C4:C17),1),">0",A4:A17,A4:A17)))


Narayan
 
WOW Narayan,


It works like a charm..

Offsets Height & Width parameter are also powerful, I never knew it.. I spend my whole day in ADDRESS and INDIRECT..

Thanks a ton buddy...


Regards,

Deb
 
One more option would be in cell C24:

=SUM((FREQUENCY(IF(INDEX($4:$17,,MATCH($A24,$3:$3,0))>=1,MATCH($A$4:$A$17,$A$4:$A$17,0),0),IF(INDEX($4:$17,,MATCH($A24,$3:$3,0))>=1,MATCH($A$4:$A$17,$A$4:$A$17,0),-1))>0)+0)

* This is an Array formula so you need to perform CTRL + SHIFT + ENTER and not just ENTER.


Copy down the formula.
 
Thanks.. Shrivallabha,


I am currently reading some of your threads at VBA Express, they are amazing and high class..

So, I am not surprise, with this formula.. :)

Just joking.. Its also mind blowing..

God bless you..


Regards,

Deb
 
Hi Deb,


Thanks for the appreciation. This is something I do as my pastime. There is no direct benefit in my day to day work but as and when it comes it pays to know Excel better.


Shrivallabha
 
Hi All,


I have a minor tweak to the original problem here, and I'm having trouble executing it. I need to input one additional layer into the "CountIf", or rather one additional condition on which to count from.


Still need to find total UNIQUE fruit, by family, ">0", but now I want to add "By Store". Basically, if the Johnson family ate at least one Apple from Safeway on multiple occasions, it should only be counted once.


Please see the spreadsheet below. The existing formula for unique fruit "={SUM(IF(OFFSET...)))}" is inputted for for the "Total Unique Fruit" column for reference.


Thanks for your help!

https://www.dropbox.com/s/simzcy1zrq25azr/Unique%20Countifs%20Sample%2011.1.12.xlsx
 
Hi dkopy,


Its a pleasure to welcome you back.. :)


Can you please try the below for me.. and confirm if its working as per requirement.. ;)

[pre]
Code: 
{=SUM(IF(($B$4:$B$19=D$23)*(INDEX($4:$19,,MATCH($A24,$3:$3,0))>0),
1/COUNTIFS($A$4:$A$19,$A$4:$A$19,$B$4:$B$19,D$23,INDEX($4:$19,,MATCH($A24,$3:$3,0)),">"&0)))}
[/pre]

* This is an Array formula so you need to perform CTRL + SHIFT + ENTER and not just ENTER.


https://dl.dropbox.com/u/78831150/Excel/Unique%20Countifs%20Sample%2011.1.12%20%28dkopy%29.xlsx


Regards,

Deb
 
Hi Dmitry,


Thank you those kind words.. These kind words are the only reward's which motivates us to help you better.. :)


Regards,

Deb
 
You must log in or register to reply here.
Back
Top