CSE formula to count unique instances

[doug firr]

Hello, I'm using a formula that I was kindly given once on this forum. It has served me well - I have cut n paste it many times and it has always worked till now.

It's an array formula (CSE).

SUM(IF(FREQUENCY(IF('all regs'!$C$10:$C$74865>=summary!$C$7,IF('all regs'!$C$10:$C$74865>=summary!$D$7,IF('all regs'!$D$10:$D$74865=summary!$B10,MATCH('all regs'!$Q$10:$Q$74865,'all regs'!$Q$10:$Q$74865,0)))),ROW('all regs'!$Q$10:$Q$74865)-ROW('all regs'!$Q$10)+1),1))​

What is says is filter down to within a date range (C7 and D7 are dates) and then filter down to match a text condition (B10 is a text string). Then count the unique records in the range Q10 - Q74,865 on the allregs tab.

By using auto filter and "remove duplicates" feature I know that I should have 3,413 unique observations but my formula returns 314.

I appreciate this may be a longshot without seeing the data - if anyone can see anything wrong with the formula do tell me!

Thanks for any feedback,
Doug.

 

[Marcelo Branco]

Maybe...

=SUM(IF(FREQUENCY(IF('all regs'!$C$10:$C$74865>=summary!$C$7,IF('all regs'!$C$10:$C$74865<=summary!$D$7,IF('all regs'!$D$10:$D$74865=summary!$B10,MATCH('all regs'!$Q$10:$Q$74865,'all regs'!$Q$10:$Q$74865,0)))),ROW('all regs'!$Q$10:$Q$74865)-ROW('all regs'!$Q$10)+1),1))

Ctrl+Shift+Enter

M.

 

[Aladin Akyurek]

Hello, I'm using a formula that I was kindly given once on this forum. It has served me well - I have cut n paste it many times and it has always worked till now.

It's an array formula (CSE).

SUM(IF(FREQUENCY(IF('all regs'!$C$10:$C$74865>=summary!$C$7,IF('all regs'!$C$10:$C$74865>=summary!$D$7,IF('all regs'!$D$10:$D$74865=summary!$B10,MATCH('all regs'!$Q$10:$Q$74865,'all regs'!$Q$10:$Q$74865,0)))),ROW('all regs'!$Q$10:$Q$74865)-ROW('all regs'!$Q$10)+1),1))​

What is says is filter down to within a date range (C7 and D7 are dates) and then filter down to match a text condition (B10 is a text string). Then count the unique records in the range Q10 - Q74,865 on the allregs tab.

By using auto filter and "remove duplicates" feature I know that I should have 3,413 unique observations but my formula returns 314.

I appreciate this may be a longshot without seeing the data - if anyone can see anything wrong with the formula do tell me!

Thanks for any feedback,
Doug.

The issue might be with the date conditions:

>=C7 vs <=D7

So, try:
=SUM(IF(FREQUENCY(IF('all regs'!$Q$10:$Q$74865<>"",IF('all regs'!$C$10:$C$74865>=summary!$C$7,IF('all regs'!$C$10:$C$74865<=summary!$D$7,IF('all regs'!$D$10:$D$74865=summary!$B10,MATCH('all regs'!$Q$10:$Q$74865,'all regs'!$Q$10:$Q$74865,0))))),ROW('all regs'!$Q$10:$Q$74865)-ROW('all regs'!$Q$10)+1),1))

Just a side note: HTML is the worst language ever invented.

 

[doug firr]

Thanks Marcelo and Aladin - can't believe I did not notice that!

 

[Marcelo Branco]

Thanks Marcelo and Aladin - can't believe I did not notice that!

You are welcome and thanks for the feedback.

M.