# Count unique occurances based on multiple conditions in another cells

• Hi,

I am trying to count the number of unique entries in a sheet, that also satisfy 2 other conditions.

I've attached an example sheet and the result I need is:
The number of unique values in column E, that also have NULL in column G and NULL in column I.

So, in the example, the result would be 7.

I know I can do this by conditional formatting and filtering, but would prefer a formula, and perhaps also a macro that I could apply as the number of rows in each sheet is up to 20000, and each month's sheet will be a different size.

Any help would be greatly appreciated!

## Files

• Re: Count unique occurances based on multiple conditions in another cells

Hello

This formula in K2 and filled down should do what you want, but it produces a count of 9

=--AND(COUNTIF(\$E\$2:E2,E2)=1,G2="NULL",I2="NULL")

Edit: minor tweak required, will post amendment shortly

Edit 2: trickier than I thought, amendment will take longer than I thought

• Re: Count unique occurances based on multiple conditions in another cells

Must be missing something...
Using the Email Id, I see 10 id's that have both G and I as NULL that are not duplicates of one another...

• Re: Count unique occurances based on multiple conditions in another cells

Hi,

Sorry, I should have been clearer.

I only want to count the Email ID's that do not have duplicate entries, but that also have NULL in columns G and I - in the example, that is 7.

Then I want the total count to appear on another sheet, so rather than putting in a formula into Column K, I want a cell within a summary table on another sheet to just display the total number of records that satisfy all 3 conditions.

• Re: Count unique occurances based on multiple conditions in another cells

posted at same time as you nicfitz, please disregard.

• Re: Count unique occurances based on multiple conditions in another cells

Thanks, sorry for misunderstanding. Just looking at it to see what's going on...

• Re: Count unique occurances based on multiple conditions in another cells

I can only do this with a helper column

So this is my shot at it:

This formula in K2 copied down, you can hide column K
=--AND(COUNTIF(\$E\$2:\$E\$23,E2)=1,G2="NULL",I2="NULL")

and then this formula on the cell of the summary sheet
=SUM(Sheet1!K:K) (change sheet name accordingly)

• Re: Count unique occurances based on multiple conditions in another cells

Hi..

This yields a result of 7 when applied to your sample Workbook..
Count result is shown in K1.

• Re: Count unique occurances based on multiple conditions in another cells

Thanks to you both!

## Participate now!

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