Uniqe value counter returns #N/A error
 

I use following array formula to count unique values in range:

=SUM(IF(FREQUENCY(IF(LEN(P76:P79)0;MATCH(P76:P79; P76:P79;0);"");
IF(LEN(P76:P79)0;MATCH(P76:P79;P76:P79;0);""))0; 1))

IT works well if P76:P79 contains simple entered values, but returns N/A
error if cells have their own formulas. In my case in range P76:P79 i have
formula:

=IF($L76=1;HLOOKUP("Summa";INDIRECT(res.table);$M7 6);"")

which works perfectly well and returns following values:
P
76 "k"
77 "k"
78 "" <- (blank text)
79 "" <- (blank text)

I can't understand why does unique values formula fail to return value 1.
It does not fail if i replace formulas with their values (by copy, paste
values only) e.g
If range conain simple values unique value counter work's well, but if range
contains formulas it returns N/A eroor

How can i solve this problem ?

This seems to work for me.

NOW, you *do* mention that this is an *array* formula, which means that you
*do* know to use CSE to register the formula ... is that right?
--
Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the
regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.

You might try a *non-array* formula instead, which *doesn't* need CSE:

=SUMPRODUCT((P76:P79<"")/COUNTIF(P76:P79;P76:P79&""))
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"Mikus" wrote in message
...
I use following array formula to count unique values in range:

=SUM(IF(FREQUENCY(IF(LEN(P76:P79)0;MATCH(P76:P79; P76:P79;0);"");
IF(LEN(P76:P79)0;MATCH(P76:P79;P76:P79;0);""))0; 1))

IT works well if P76:P79 contains simple entered values, but returns N/A
error if cells have their own formulas. In my case in range P76:P79 i have
formula:

=IF($L76=1;HLOOKUP("Summa";INDIRECT(res.table);$M7 6);"")

which works perfectly well and returns following values:
P
76 "k"
77 "k"
78 "" <- (blank text)
79 "" <- (blank text)

I can't understand why does unique values formula fail to return value 1.
It does not fail if i replace formulas with their values (by copy, paste
values only) e.g
If range conain simple values unique value counter work's well, but if range
contains formulas it returns N/A eroor

How can i solve this problem ?

Thanks for your time... but your formula does not help either... I still get
N/A and it looks like this time it even does not work with replacing formula
with values either

Maybe my explanation of this problem is not good enough?
Could you take a look at my excel spreadsheet for a minute ... who knows
maybe it helps to come up with right answer right away!?
You can download it @ http://www.svara-kontrole.lv/accounting_rec_v0.3.xls

The problematic formula is located in cell I76 @ spreadsheet called
"Grāmatojumi" Formula should count unique values in range P76:P79


"RagDyeR" wrote:

This seems to work for me.

NOW, you *do* mention that this is an *array* formula, which means that you
*do* know to use CSE to register the formula ... is that right?
--
Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the
regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.

You might try a *non-array* formula instead, which *doesn't* need CSE:

=SUMPRODUCT((P76:P79<"")/COUNTIF(P76:P79;P76:P79&""))
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"Mikus" wrote in message
...
I use following array formula to count unique values in range:

=SUM(IF(FREQUENCY(IF(LEN(P76:P79)0;MATCH(P76:P79; P76:P79;0);"");
IF(LEN(P76:P79)0;MATCH(P76:P79;P76:P79;0);""))0; 1))

IT works well if P76:P79 contains simple entered values, but returns N/A
error if cells have their own formulas. In my case in range P76:P79 i have
formula:

=IF($L76=1;HLOOKUP("Summa";INDIRECT(res.table);$M7 6);"")

which works perfectly well and returns following values:
P
76 "k"
77 "k"
78 "" <- (blank text)
79 "" <- (blank text)

I can't understand why does unique values formula fail to return value 1.
It does not fail if i replace formulas with their values (by copy, paste
values only) e.g
If range conain simple values unique value counter work's well, but if range
contains formulas it returns N/A eroor

How can i solve this problem ?

You need to use the same range

=SUMPRODUCT((P76:P79<"")/COUNTIF(P76:P79,P76:P79&""))

returns 1

the formula in your workbook is

=SUMPRODUCT((I78:I91<"")/COUNTIF(P76:P79,P76:P79&""))

--
Regards,

Peo Sjoblom

(No private emails please)


"Mikus" wrote in message
...
Thanks for your time... but your formula does not help either... I still
get
N/A and it looks like this time it even does not work with replacing
formula
with values either

Maybe my explanation of this problem is not good enough?
Could you take a look at my excel spreadsheet for a minute ... who knows
maybe it helps to come up with right answer right away!?
You can download it @ http://www.svara-kontrole.lv/accounting_rec_v0.3.xls

The problematic formula is located in cell I76 @ spreadsheet called
"Grāmatojumi" Formula should count unique values in range P76:P79


"RagDyeR" wrote:

This seems to work for me.

NOW, you *do* mention that this is an *array* formula, which means that
you
*do* know to use CSE to register the formula ... is that right?
--
Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of
the
regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.

You might try a *non-array* formula instead, which *doesn't* need CSE:

=SUMPRODUCT((P76:P79<"")/COUNTIF(P76:P79;P76:P79&""))
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"Mikus" wrote in message
...
I use following array formula to count unique values in range:

=SUM(IF(FREQUENCY(IF(LEN(P76:P79)0;MATCH(P76:P79; P76:P79;0);"");
IF(LEN(P76:P79)0;MATCH(P76:P79;P76:P79;0);""))0; 1))

IT works well if P76:P79 contains simple entered values, but returns N/A
error if cells have their own formulas. In my case in range P76:P79 i
have
formula:

=IF($L76=1;HLOOKUP("Summa";INDIRECT(res.table);$M7 6);"")

which works perfectly well and returns following values:
P
76 "k"
77 "k"
78 "" <- (blank text)
79 "" <- (blank text)

I can't understand why does unique values formula fail to return value 1.
It does not fail if i replace formulas with their values (by copy, paste
values only) e.g
If range conain simple values unique value counter work's well, but if
range
contains formulas it returns N/A eroor

How can i solve this problem ?

In case of a range with #N/A's...

(a) If you have the latest version of Longre's morefunc.xll add-in:

=COUNTDIFF(IF(ISNA(P76:P79),0,P76:P79),FALSE,0)

(b) With built-in functions:

=SUM(IF(ISNUMBER(--(P76:P79<"")),--(IF(ISNA(P76:P79),FALSE,MATCH(P76:P79&"",P76:P79&" ",0))=ROW(INDEX(P76:P79,0,0))-ROW(P76)+1)))

These formulas must be confirmed with control+shift+enter, not just with
enter.

Mikus wrote:
I use following array formula to count unique values in range:

=SUM(IF(FREQUENCY(IF(LEN(P76:P79)0;MATCH(P76:P79; P76:P79;0);"");
IF(LEN(P76:P79)0;MATCH(P76:P79;P76:P79;0);""))0; 1))

IT works well if P76:P79 contains simple entered values, but returns N/A
error if cells have their own formulas. In my case in range P76:P79 i have
formula:

=IF($L76=1;HLOOKUP("Summa";INDIRECT(res.table);$M7 6);"")

which works perfectly well and returns following values:
P
76 "k"
77 "k"
78 "" <- (blank text)
79 "" <- (blank text)

I can't understand why does unique values formula fail to return value 1.
It does not fail if i replace formulas with their values (by copy, paste
values only) e.g
If range conain simple values unique value counter work's well, but if range
contains formulas it returns N/A eroor

How can i solve this problem ?

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.