Count distinct values where data changes everyday

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

You can use a dynamic range.

What type of data is in that range? Is it text? Numbers? Could be both? Something else?

Try this...

Goto the Formulas tab>Define Name

Name: MyRange (or whatever name you want to use)
Refers to:

=Resolved!$M$2:INDEX(Resolved!$M:$M,MATCH("zzzzz",Resolved!$M:$M))

OK out

Then, in your formula you would use MyRange like this:

=IF(SUMPRODUCT(1/COUNTIF($J$17:$J$24,$J17:$J24))=SUMPRODUCT(1/COUNTIF(MyRange, MyRange)), "Macro working", "Macro won't work")

We're using the lookup value zzzzz to find the last (bottom-most) TEXT entry in the column.

In Excel text has value just as numbers have value. 2 is greater than 1 and 3 is greater than 2.

In that same manner text has value such that B is greater than A and C is greater than B. You can see this when you sort something.

So, with that in mind...

The lookup value zzzzz has a very high value. In this formula:

MATCH("zzzzz",Resolved!$M:$M)

If the lookup value zzzzz is greater than any TEXT entry in the range, MATCH will "find" the last (bottom-most) TEXT entry in the range.

So, if your text data is "standard" text entries like names or common words then the lookup value of zzzzz is going to have a greater value and the formula works as we expect it should.

You're welcome!

To define a dynamic range that contains numeric values...

=SLA_Countdown!$A$2:INDEX(SLA_Countdown!$A:$A,MATCH(1E100,SLA_Countdown!$A:$A))

Your formula may work but the one above is more efficient (faster to calculate).