Both formulas return the number of unique values in the range.
Say the formulas are in H1 and H2.

{=SUM(1/COUNTIF(A1:F5,A1:F5))}

=SUM(IF(FREQUENCY(A1:F5,A1:F5)0,1))

With the value 3 in cell G1, how can I make either/both of these formulas change F5 to F3.

So, vary the range from A1 to F? by the value in G1.

Thanks.
Howard

Both formulas return the number of unique values in the range.
Say the formulas are in H1 and H2.

{=SUM(1/COUNTIF(A1:F5,A1:F5))}

=SUM(IF(FREQUENCY(A1:F5,A1:F5)0,1))

With the value 3 in cell G1, how can I make either/both of these
formulas change F5 to F3.

So, vary the range from A1 to F? by the value in G1.

Thanks.
Howard

Take a look at the INDIRECT() function...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Take a look at the INDIRECT() function...

--
Garry

I actually did try that, formula looks like this and returns #NAME.

=SUM(IF(FREQUENCY(A1:F&INDIRECT(G1),A1:F&INDIRECT( G1))0,1))

Must have the syntax wrong.

Howard

Take a look at the INDIRECT() function...

--
Garry

I actually did try that, formula looks like this and returns #NAME.

=SUM(IF(FREQUENCY(A1:F&INDIRECT(G1),A1:F&INDIRECT( G1))0,1))

Must have the syntax wrong.

Howard

Your absolutely correct! This function returns a value from the range
it refs, or from the range named in G1 such as you've used in dependant
dropdowns. The help file states ref_text is how to specify and so
try...

INDIRECT("G1")

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

On Tuesday, February 24, 2015 at 8:59:57 PM UTC-8, GS wrote:
Take a look at the INDIRECT() function...

--
Garry

I actually did try that, formula looks like this and returns #NAME.

=SUM(IF(FREQUENCY(A1:F&INDIRECT(G1),A1:F&INDIRECT( G1))0,1))

Must have the syntax wrong.

Howard

Your absolutely correct! This function returns a value from the range
it refs, or from the range named in G1 such as you've used in dependant
dropdowns. The help file states ref_text is how to specify and so
try...

INDIRECT("G1")

--
Garry


Looks like the crowd is going with this, which works.

=SUM(IF(FREQUENCY(A1:INDEX(F:F,G1),A1:INDEX(F:F,G1 )),1))

I was unable to get the indirect("G1") to work.

Howard

Hi Howard,

Am Tue, 24 Feb 2015 21:31:56 -0800 (PST) schrieb L. Howard:

=SUM(IF(FREQUENCY(A1:INDEX(F:F,G1),A1:INDEX(F:F,G1 )),1))

try:

=SUM(1/COUNTIF(INDIRECT("A1:F"&G1),INDIRECT("A1:F"&G1)))
=SUM(IF(FREQUENCY(INDIRECT("A1:F"&G1),INDIRECT("A1 :F"&G1))0,1))


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hi Howard,

Am Tue, 24 Feb 2015 21:31:56 -0800 (PST) schrieb L. Howard:

=SUM(IF(FREQUENCY(A1:INDEX(F:F,G1),A1:INDEX(F:F,G1 )),1))

try:

=SUM(1/COUNTIF(INDIRECT("A1:F"&G1),INDIRECT("A1:F"&G1)))
=SUM(IF(FREQUENCY(INDIRECT("A1:F"&G1),INDIRECT("A1 :F"&G1))0,1))


Regards
Claus B.

Ahh! That looks like it! The ref_text is in double quotes and the cell
ref will be appended as text. This is the same syntax as
Range("A1:F"&G1) where the row value is stored in G1! Without the
double quotes I suspect XL is wanting a range name...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

try:

=SUM(1/COUNTIF(INDIRECT("A1:F"&G1),INDIRECT("A1:F"&G1)))
=SUM(IF(FREQUENCY(INDIRECT("A1:F"&G1),INDIRECT("A1 :F"&G1))0,1))


Regards
Claus B.

Hmm, this one gives me a #DIV/0!
I tried C + S + E to enter also, but still gives dividing by 0

=SUM(1/COUNTIF(INDIRECT("A1:F"&G1),INDIRECT("A1:F"&G1)))

The other works well. The " "'s are very tricky indeed!

Thanks, guys.

Howard

Hi Howard,

Am Wed, 25 Feb 2015 02:02:46 -0800 (PST) schrieb L. Howard:

Hmm, this one gives me a #DIV/0!
I tried C + S + E to enter also, but still gives dividing by 0

if you have entered the formula with Ctrl+Shift+Enter you entered in G1
a value that is greater than your rows count or the range in your
formula is to big.
If you insert in your formula:
INDIRECT("A1:F"&G1) and column F is empty you get this error message.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

On Wednesday, February 25, 2015 at 2:12:13 AM UTC-8, Claus Busch wrote:
Hi Howard,

Am Wed, 25 Feb 2015 02:02:46 -0800 (PST) schrieb L. Howard:

Hmm, this one gives me a #DIV/0!
I tried C + S + E to enter also, but still gives dividing by 0

if you have entered the formula with Ctrl+Shift+Enter you entered in G1
a value that is greater than your rows count or the range in your
formula is to big.
If you insert in your formula:
INDIRECT("A1:F"&G1) and column F is empty you get this error message.


Regards
Claus B.

I find that there can be no blanks within the range, or it errors.

The other formula does fine and can have blank cells, blank row or blank column.
Much more versatile.

Thanks,
Howard