NG wrap-around caused it I presume?

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Kent (thanks)" wrote in message
...
Bob,

I found my error. Your formula works great!

Thanks for the help!

Kent

"Kent (thanks)" wrote:

Bob,

I have used the formula you indicated below. In my spreadsheet, the
actual
data that I want to count is located in cells B10:B82 with my "X"s being
located in cells R10:R82. When I plug in these cell ranges into the
formula
below, I get an error in that it does not appear to like the
(indirect("1:

Any ideas?

Thanks,
Kent

"Bob Phillips" wrote:


=SUM(--(FREQUENCY(IF(B1:B10="X",MATCH(A1:A10,A1:A10,0)),R OW(INDIRECT("1:"&RO
WS(A1:A10))))0))

which is an array formula, it should be committed with
Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Kent (thanks)" wrote in
message
...
You are correct, there are 4 unique values in column A, but I only
want
them
to be counted if there is a corresponding (X) in column B. Thus,
based on
my
example, "3" should not be counted as a unique value as there is no
corresponding "X" in column B.

"JethroUK©" wrote:

There are 4 unique values in your example of column A:

you can count them with array formula:

=SUM(COUNTIF(A1:A10,A1:A10)/COUNTIF(A1:A10,A1:A10)^2)

but i think you need to describe exactly what you mean by "..based
on
set
criteria in Column B.."



"Kent (thanks)" wrote in
message
...
I have a spreadsheet where I am trying to count the number of
unique
values
in Column A, based on set criteria in Column B. I have searched
all
over
the
internet and the most popular solution seems to be the formula
below.
For
examples purposes assume the following:
A B
1 X
2 Y
2 X
3 Y
3 Y
4 X
4 X
4 X

I use the following array formula to count the number of unique
numbers in
column A, based on (X) in column B.

=SUM(1/COUNTIF(A1:A8,A1:A8)*(B1:B8="x"))

I would expect the result to be 3, but instead I get 2.5

Any suggestions on how to correct my formula? My actual
spreadsheet
includes both numeric and alpha fields that need to be counted,
but no
blank
spaces.

Thanks in advance for your assistance,