Countifs (2 critera) but only once and do not count duplicates.

Hello All,

Please see attached sheet,

Sheet 1, Column V is where i would like the formula. Sheet 2 is the reference table

I would like to count the number of unique times that the Cost Center number comes up and ignores duplicates.

For example:
10064 is 1 because the Reference is only AAA00007071
10078 is 2 because the Reference has two unique numbers AAA00006600 and AAA00007170

I manally entered in Column W so you can see what values i'm looking for.

Thanks for the help!Countifs-Unique.xlsx

Originally Posted by Ace_XL

In V5 try this array formula

=SUM(IF(FREQUENCY(IF('REFERENCE WC_Assigned'!$B$5:$B$116=B5,MATCH('REFERENCE WC_Assigned'!$A$5:$A$116,'REFERENCE WC_Assigned'!$A$5:$A$116,0)),ROW($A$5:$A$116)-ROW(A5)),1,0))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

Copy down

Formula works with my sample sheet.. but when i tried to place it into my actual sheets they do not.

My Sheet 'REFERENCE WC_Assigned' the values go down to 12200. But this may change so i tried to add more range to 12500 to account for it and changed your formula to:

=SUM(IF(FREQUENCY(IF('REFERENCE WC_Assigned'!$B$5:$B$12500=B5,MATCH('REFERENCE WC_Assigned'!$A$5:$A$12500,'REFERENCE WC_Assigned'!$A$5:$A$12500,0)),ROW($A$5:$A$12500)-ROW(A5)),1,0))


And now it doesn't seem to work. Any thoughts?

Countifs-Unique (2).xlsxI made sure it was an array.

Not sure if i got the Match right but tried that.

Here is the full data.. maybe it helps.

Has anyone been able to find a solution?

Thanks the help is much apprecaited.

Hi Tony,

Actually the original file posted was a small sample size. I'm not sure why it is bigger then 50kb tho. However the formula provided seemed to work but not in the larger format which was posted for reference. If it would make it easier I could e-mail you the larger (265kb) directly to you?

Thanks