# Count unique values in one column given multiple criteria for other columns

1. ## Count unique values in one column given multiple criteria for other columns

I looked through the forum, and couldn't find anything exactly like my problem.

I cannot use VBA.

My problem is this:

I have a spreadsheet with multiple columns: Column A, B, C, D, etc. I need to know the sum of UNIQUE values in column A where certain conditions are met in Columns C, D, E, etc.

For one row, I expect the forumula would look something like the following:

If (Column B == "crit1"){
if (Column C == "crit2"){
if (Column D != "crit3"){
count_unique_values(Column A);
}
}
}

Of course, nothing like this will work since my sheet has multiple rows with a mix of values.

Note that only UNIQUE values in column A shall be counted.

How could I expand the equation to include additional criteria in other columns?

Thanks in advance! Please let me know if there are any questions.

2. ## Re: Count unique values in one column given multiple criteria for other columns

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

3. ## Re: Count unique values in one column given multiple criteria for other columns

Attached is a dummy workbook of what I am trying to achieve. For the sake of example, let's say that I am trying to count unique values in Column A where values in Column B = "g", C = "3", and D != "y".

Also, blank values in Column A should be ignored.

As shown in highlighting within the workbook, three values meet the criteria: "1", "cat", and "cat". However, the function needs to return "2" since that is the sum of UNIQUE values.

I hope this clarifies things.

Thanks.

4. ## Re: Count unique values in one column given multiple criteria for other columns

I can't see how you could do it without introducing a helper column. My attempt would be to list the items that meet your criteria in a column, I've used column F which has an array formula to pull through all values that meet the criteria, then in column G a count of the unique. Is this close to what you need? I had already made up my sample before you posted, i'm sure you can adapt to meet your layout. If it's just the count you need then you can hide column F

ps the Array Formula needs to be entered with Ctrl + Shift + Enter, not just enter

5. ## Re: Count unique values in one column given multiple criteria for other columns

I can't see how you could do it without introducing a helper column. My attempt would be to list the items that meet your criteria in a column, I've used column F which has an array formula to pull through all values that meet the criteria, then in column G a count of the unique. Is this close to what you need? I had already made up my sample before you posted, i'm sure you can adapt to meet your layout. If it's just the count you need then you can hide column F

I've added some CF to make it easier to look at

6. ## Re: Count unique values in one column given multiple criteria for other columns

Scotty,

Thanks for the reply. That is exactly what I want, except without the helper column.

I had a similar request a bit back, here is the URL: http://www.excelforum.com/excel-gene...e-columns.html

Similarly to now, I was looking for a way to count unique entities in one column based on requirements being met in other columns. The difference is that I now need the function to check an additional column for values NOT equal to some criterion.

What you did is exactly what I asked for. I apologize for not specifying that I cannot use a helper column.

Thanks.

7. ## Re: Count unique values in one column given multiple criteria for other columns

ok no problem. For what it's worth I don't think you could do this in a non VBA scenario without a helper column, trying to incorporate an array formula to extract the criteria matching records and then count the unique instances of items from within the results is bound to throw up a circular reference.

But there's some pretty smart people on here who continue to prove there's more than one way to crack a nut

good luck

8. ## Re: Count unique values in one column given multiple criteria for other columns

See the attached.

9. ## Re: Count unique values in one column given multiple criteria for other columns

Haseeb,

Thank you for your work. This is very close to what I need.

How difficult would it be to change the equation to, rather than select rows in column D that contain the value given in cell I3, instead select all rows EXCEPT those that contain I3? In other words, if I wanted to select all unique values in column A where values in B and C match specified values, and values in D include all values except the specified value.

Also, would it be possible to make the inputs of the function include ranges rather than single cells? For example, if I wanted to select rows where the values in column B are "4", "g", or "d", instead of only "4"?

Once again, thank VERY much you for your work. Your help is greatly appreciated.

*bump*

Anybody?

11. ## Re: Count unique values in one column given multiple criteria for other columns

See Sheet2 in attached.

Does this help?

12. ## Re: Count unique values in one column given multiple criteria for other columns

Haseeb,

This works perfectly and is EXACTLY what I needed! Thanks so much!

There are currently 1 users browsing this thread. (0 members and 1 guests)