Count distinct values where data changes everyday

[jonathan.haynes]

Hi,

I'm trying to count the distinct values within a list. My formula currently looks like this

=IF(SUMPRODUCT(1/COUNTIF($J$17:$J$24,$J17:$J24))=SUMPRODUCT(1/COUNTIF(Resolved!$M$2:$M$53, Resolved!$M$2:$M53)), "Macro working", "Macro won't work")

Currently it is counting the data in J17:J24 on the summary page (page where the forumla is). And it is also counting the number of unique values on the resolved tab, data M2:M53.

Stepping in, it actually looks like:

IF(8 = 8) [8 unique values in each list] then bring me back "Macro working". However if 8 <> 8, bring me back "Macro won't work".

-

The problem I am having is that the number of data in the second part of the SUMPRODUCT(1/COUNTIF( changes everyday. I need a workaround where I can simply put there M2:M500 for example, so I don't have to change the formula each time.


So really what I want the formula to say is:

=IF(SUMPRODUCT(1/COUNTIF($J$17:$J$24,$J17:$J24))=SUMPRODUCT(1/COUNTIF(Resolved!$M$2:$M$500, Resolved!$M$2:$M500)), "Macro working", "Macro won't work")

Any workarounds would be great, or help!

Many thanks