# combine countifs and unique function.

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

Supat,

Thanks, and yes as Luke says, for Countifs to work properly you need to have the right size array.

~VijaySharma

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

Hi jpapex,

Whoops! You have not read the rules on posting - please refer to the forum home page and read the green sticky posts. Then repost your question on a new post.

http://chandoo.org/forums/?new=1

Thanks

Last edited by a moderator:
Thanks oldchippy,

I have done so.