Count unique text values in excel range with blank cells

[nostradamus]

Hello, I have used this formula for counting unique text values, but would like to set the formula for a spreadsheet where I can copy and paste data to get the unique occurances.
But the problem I found is that I have to set the range manually each time for this formula to work properly.
For instance, here the cell range to be counted is from A2 to A26

=SUMPRODUCT(--(FREQUENCY(MATCH(A2:A26,A2:A26,0),ROW(A2:A26)-ROW(A2)+1)>0))

I would like to set the range from cells A2 to A1000, when the actual data could only be for 100 cells or 500 cells depending on the dataset I use.
So, when I set the data range in the formula upto A1000, "N/A" is my result.

=SUMPRODUCT(--(FREQUENCY(MATCH(A2:A1000,A2:A1000,0),ROW(A2:A22000)-ROW(A2)+1)>0))

Thanks in advance

 

[Fluff]

How about

=SUMPRODUCT(--(FREQUENCY(IF(A2:A1000<>"",MATCH(A2:A1000,A2:A1000,0)),ROW(A2:A1000)-ROW(A2)+1)>0))

What version of Excel are you using?
Please update your account details to show this, as it affects which functions you can use.

 

[nostradamus]

How about

=SUMPRODUCT(--(FREQUENCY(IF(A2:A1000<>"",MATCH(A2:A1000,A2:A1000,0)),ROW(A2:A1000)-ROW(A2)+1)>0))

What version of Excel are you using?
Please update your account details to show this, as it affects which functions you can use.

It's Office 365

 

[Peter_SSs]

It's Office 365

Please add this to your profile as requested in your other thread.

Try

=ROWS(UNIQUE(FILTER(A2:A1000,A2:A1000<>"")))

 

[nostradamus]

How about

=SUMPRODUCT(--(FREQUENCY(IF(A2:A1000<>"",MATCH(A2:A1000,A2:A1000,0)),ROW(A2:A1000)-ROW(A2)+1)>0))

What version of Excel are you using?
Please update your account details to show this, as it affects which functions you can use.

This formula worked, thanks!!!

 

[Fluff]

You're welcome & thanks for the feedback.
However as you are using 365 I would go with Peter's suggestion.

 

[nostradamus]

Please add this to your profile as requested in your other thread.

Try

=ROWS(UNIQUE(FILTER(A2:A1000,A2:A1000<>"")))

Updated my profile as you suggested, thanks.

For some reason the formula you provided did not work, it is giving me a much higher number than is. Formula given by Fluff worked though.

=SUMPRODUCT(--(FREQUENCY(IF(A2:A1000<>"",MATCH(A2:A1000,A2:A1000,0)),ROW(A2:A1000)-ROW(A2)+1)>0))

 

[Fluff]

They both give the same result for me.

+Fluff v2.xlsm
	A	B	C	D
1	County		Frequency	Filter
2	Cumbria		10	10
3	Shropshire
4	West Yorkshire
5	County Durham
6	Staffordshire
7	Northumberland
8	South Yorkshire
9	West Yorkshire
10	West Yorkshire
11	Lancashire
12	West Yorkshire
13	Devon
14	County Durham
15	North Yorkshire
16	North Yorkshire
17	Staffordshire
18	West Yorkshire
19	West Yorkshire
20	County Durham
21	Cumbria
22	Lancashire
23	West Yorkshire
24	West Yorkshire
25	West Yorkshire
26
Main

Cell Formulas
Range	Formula
C2	C2	=SUMPRODUCT(--(FREQUENCY(IF(A2:A1000<>"",MATCH(A2:A1000,A2:A1000,0)),ROW(A2:A1000)-ROW(A2)+1)>0))
D2	D2	=ROWS(UNIQUE(FILTER(A2:A1000,A2:A1000<>"")))

 

[nostradamus]

They both give the same result for me.

+Fluff v2.xlsm
	A	B	C	D
1	County		Frequency	Filter
2	Cumbria		10	10
3	Shropshire
4	West Yorkshire
5	County Durham
6	Staffordshire
7	Northumberland
8	South Yorkshire
9	West Yorkshire
10	West Yorkshire
11	Lancashire
12	West Yorkshire
13	Devon
14	County Durham
15	North Yorkshire
16	North Yorkshire
17	Staffordshire
18	West Yorkshire
19	West Yorkshire
20	County Durham
21	Cumbria
22	Lancashire
23	West Yorkshire
24	West Yorkshire
25	West Yorkshire
26
Main

Cell Formulas
Range	Formula
C2	C2	=SUMPRODUCT(--(FREQUENCY(IF(A2:A1000<>"",MATCH(A2:A1000,A2:A1000,0)),ROW(A2:A1000)-ROW(A2)+1)>0))
D2	D2	=ROWS(UNIQUE(FILTER(A2:A1000,A2:A1000<>"")))

You are right, sorry about that. I had a typo in my transferred formula (need more ). Both the formulas work great, thanks for taking the time

 

[Fluff]

Glad we could help & thanks for the feedback.