• 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


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


New Member

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.



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!



Put this formula in Col C


Then put this formula in Col D


Now assuming cell G2 contains your search string AB or LK

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


Change AB or LK in G2 to get the results.

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.



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.

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.



here you go....


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.

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

Just for curiousity, would you know why such formula


won't give the desired results?

Thanks again, and really appreciate your help!

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.