[SOLVED] SumProduct of Unique values in a dynamic length column

I would like to make these formulas a more 'user friendly' since the data is dynamic from day to day.

My formulas are:
0: =SUMPRODUCT((B2:B246<>"")/COUNTIF(B2:B246,B2:B246&""))
1: =SUMPRODUCT((B247:B2242<>"")/COUNTIF(B247:B2242,B247:B2242&""))
2: =SUMPRODUCT((B2243:B4763<>"")/COUNTIF(B2243:B4763,B2243:B4763&""))
3: =SUMPRODUCT((B4764:B8216<>"")/COUNTIF(B4764:B8216,B4764:B8216&""))
4: =SUMPRODUCT((B8217:B8320<>"")/COUNTIF(B8217:B8320,B8217:B8320&""))
5: =SUMPRODUCT((B8321:B8322<>"")/COUNTIF(B8321:B8322,B8321:B8322&""))

I'm counting the unique values in column B where column K has a 0, 1, 2, 3, 4, or 5.
The data I want to do the uniques count on will always start in B2, but one day there will be a 0 in column K down to row B246, K = 1 from row B247:B2242, K = 2 from row B2243:B4763, etc, then next day K = 0 from row B2:B432, K = 1 from B433:B3200, etc

How can I change format of these formulas to autmatically find the dynamic range of 0, 1, 2, 3, 4, 5 without manually having to update the cell values in the formulas each day?

Just because there is more than one way to find the count of unique values, I can also use these array formulas:
{=SUM(1/COUNTIF(B2:B246,B2:B246))}
{=SUM(1/COUNTIF(B247:B2242,B247:B2242))}
{=SUM(1/COUNTIF(B2243:B4763,B2243:B4763))}
{=SUM(1/COUNTIF(B4764:B8216,B4764:B8216))}
{=SUM(1/COUNTIF(B8217:B8320,B8217:B8320))}
{=SUM(1/COUNTIF(B8321:B8322,B8321:B8322))}

So the same question here, How can I make these work with a dynamic range each day without manually entering the range?

Thanks in Advance
Don

Try this:
=SUMPRODUCT((B2:B246<>"")/COUNTIF(B2:B246,B2:B246&""))
replace with

=SUMPRODUCT((INDEX(B2:B10000,MATCH(0,K2:K10000,0)):INDEX(B2:B10000, MATCH(0,K2:K10000))<>"")/COUNTIF(INDEX(B2:B10000,MATCH(2,K2:K10000,0)):INDEX(B2:B10000, MATCH(2,K2:K10000)),INDEX(B2:B10000,MATCH(0,K2:K10000,0)):INDEX(B2:B10000, MATCH(0,K2:K10000))&""))

Others are similar, just replace your range of Bx:By with
INDEX(B2:B10000,MATCH(A,K2:K10000,0)):INDEX(B2:B10000, MATCH(A,K2:K10000)) where "A" is either 0,1,2,3,4 or 5

ChemistB,

I had to make one minor change to get the formula to work, but now it does return a value and not a Div/0 error. Two of the Match lookup values was a 2, when we were solving for 0 in the first formula. Was that my test?

It works perfectly now...

Thanks,
Don