# Unique count of values in a column satisfying multiple conditions in another column

• Hi

I require to count unique entries in excel albeit on satisfying two criterias. My data is organised like this:

Col A Col B
A Jun
A July
A Aug
B Jun
B Jul
B Jul
C Jun
C Aug
Now I want a unique count of entries in column A that fall BOTH in June AND July. So in the above case value A comes in June and July therefore the count for A should be 1. Similarly B also falls in Jun and July so the count should be 1 for B as well. However C comes in June but not in July and hence the count for C should be 0. So the final answer should be 2 achieved as (1+1+0). I would like to extend this formulae to include more criterias. Request someone to kindly help.

• Re: Unique count of values in a column satisfying multiple conditions in another colu

Hi Sir

I do not have dates entered but I have months written the same way as I shown in my example.My data entered in excelk is in the same fashion.Hope that helps.

• Re: Unique count of values in a column satisfying multiple conditions in another colu

You haven't said which version of Excel you are using, that's partly why I asked for an example workbook, plus I haven't time to recreate a workbook.

• Re: Unique count of values in a column satisfying multiple conditions in another colu

Hi Sir

Please find the workbook attached. I am using excel 2010.

## Files

• Re: Unique count of values in a column satisfying multiple conditions in another colu

Hi Murtuza

Either one of these formulas in C2 of the example workbook

=--IF(B2="Jun",COUNTIFS(\$A\$2:\$A\$10,A2,\$B\$2:\$B\$10,"Jul")>0,0)
=--IF(B2="Jun",SUMPRODUCT((\$A\$2:\$A\$10=A2)*(\$B\$2:\$B\$10="Jul"))>0,0)

Format C2 as 0;-0;;@

And then fill down

Or do you just want to show a total in one cell?

• Re: Unique count of values in a column satisfying multiple conditions in another colu

Can also be done like this

=(B2="Jun")*(COUNTIFS(\$A\$2:\$A\$10,A2,\$B\$2:\$B\$10,"Jul")>0)
=(B2="Jun")*(SUMPRODUCT((\$A\$2:\$A\$10=A2)*(\$B\$2:\$B\$10="Jul"))>0)

• Re: Unique count of values in a column satisfying multiple conditions in another colu

VBA alternative..

Hey.. maybe i like typing..

• Re: Unique count of values in a column satisfying multiple conditions in another colu

Hi
I would appreciate if I could get a formula to throw up the total count in one cell.

• Re: Unique count of values in a column satisfying multiple conditions in another colu

Hi Thanks for your reply.Can you please tell me how to execute this macro I know how to run udf's but not macros.

• Re: Unique count of values in a column satisfying multiple conditions in another colu

Ooops..

Change this line:

Code
``Set Srch = .AutoFilter.Range.Offset(1, 1).Resize(.AutoFilter.Range.SpecialCells(12).Count + 1, 1).SpecialCells(12)``

to:

Code
``Set Srch = .AutoFilter.Range.Offset(, 1).Resize(.AutoFilter.Range.SpecialCells(12).Count + 1, 1).SpecialCells(12)``

Check out the attachment in my Previous post to see where to put the code..

If you can't.. just add an ActiveX CommandButton to your Sheet and add the code to it..

• Re: Unique count of values in a column satisfying multiple conditions in another colu

UDF

Use in cell like

=UNIQIF(A2:B10,"Jun","Jul")
You can add more condition(s) like
=UNIQIF(A2:B10,"Jun","Jul","Aug","Sep","OK")

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!