• 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.

combine countifs and unique function.

S

Supat

New Member
Hello,

I am looking for a function that can give me unique count of values in one column based on condition if met in a differnt column.

Eg:

COL A COL B

AB cat

AB dog

LK horse

LK dog

LK dog

LK rat


Answer: Unique count with LK in column A is 3.


Appreciate any help!

Thanks,

Supat
 
Supat,


Put this formula in Col C



=A2&B2




Then put this formula in Col D



=A2&COUNTIF($C$2:C2,C2)




Now assuming cell G2 contains your search string AB or LK


put this formula in H2, increase your range as required.




=COUNTIF(D2:D21,G2&"1")


Change AB or LK in G2 to get the results.


~VijaySharma
 
Last edited by a moderator:
Thanks for your response. I am able to accomplish the result I need by creating a new column C to my dataset and defining =if(A2="LK",B2,"")


And then summing up = sumproduct((C2:C10<>"")*(1/countif(C2:C10,C2:C10)))


However, this looks like a roundabout solution. I am really looking for a formula that can get me a similar sumproduct result from existing columns A and B, and not creating any extra columns.


Thanks,

Supat
 
Supat,


Are you only going to have 2 values in Col A, AB and LK? or this can be more.


If more, then i do not see the purpose of using the IF condition to be serving the cause.


~VijaySharma
 
that's correct. I could have 10 different values in Col A. Using an IF condition will mean creating one such column for each such value - which is why I don't want to tread that path.


My goal is to get a summary total of unique values in Col B, for each category in Col A.


Thanks,

Supat
 
Supat,


here you go....

=SUM(IF(FREQUENCY(IF($A$2:$A$14<>"",
IF($A$2:$A$14=D2,MATCH("~"&$B$2:$B$14&"",$B$2:$B$14&"",))),
ROW($A$2:$A$14)-ROW($A$2)+1),1))


Cell D2 is where you define your search criteria (AB, LK, etc)


Increase the range as desired...


NOTE: This is a array formula, please press CTRL + SHIFT + ENTER where using this.


~VijaySharma
 
Last edited by a moderator:
This is fantastic! Thanks much Vijay Sharma.


Just for curiousity, would you know why such formula

=sumproduct(1/countifs(B2:B10,B2:B10,A2:A10,"LK")))


won't give the desired results?


Thanks again, and really appreciate your help!

Supat
 
Just taking a guess, since I don't have 2007, but in the arrays for your COUNTIFS, I'm guessing a problem is created by the first criteria being an array and the second criteria being a single value.
 
Luke, that sounds reasonable. I'll surprised though if there's not an easier fix to it. I like VijaySharma's solution, but the string is a lot complicated compared to the sumproduct one.
 
I have a really complicated issue. I am looking to count the number of unique phone numbers in a date range, for a unique inbound 800 line. So one column Sheet2!O:O contains the phone numbers and another column Sheet2!K:K contains the date ranges, and Sheet2!I:I contains the unique inbound 800 line. I am looking to display this total on another page using the Sheet 2 references. Any idea how I can do this? I already figured out how to total by date ranges and unique 800 line but this is a little more complicated with the unique values for unique phone numbers.


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