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!

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


    Thanks for your replies.


    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


    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.


Participate now!

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