Using sum(1/countif....) not returning expected result

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,

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)" <[email protected]> wrote in message
news:[email protected]...
> 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,
>
>
>
>

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)" <[email protected]> wrote in message
> news:[email protected]...
> > 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,
> >
> >
> >
> >
>
>
>

=SUM(--(FREQUENCY(IF(B1:B10="X",MATCH(A1:A10,A1:A10,0)),ROW(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)" <[email protected]> wrote in message
news:[email protected]...
> 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)" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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,
> > >
> > >
> > >
> > >
> >
> >
> >

Assuming that A2:B9 contains the data, try the following formula, which
needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER...

=COUNT(1/FREQUENCY(IF(B2:B9="X",IF(A2:A9<>"",A2:A9)),IF(B2:B9="X",IF(A2:A
9<>"",A2:A9))))

Hope this helps!

In article <[email protected]>,
Kent (thanks) <[email protected]> wrote:

> 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,

Another variation, also array entered. I got it by piecing together what I
could remember from one of Harlan's posts.

=SUM(--(FREQUENCY(IF(B1:B10="X",A1:A10,""),IF(B1:B10="X",A1:A10,""))>0))

"Bob Phillips" wrote:

> =SUM(--(FREQUENCY(IF(B1:B10="X",MATCH(A1:A10,A1:A10,0)),ROW(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)" <[email protected]> wrote in message
> news:[email protected]...
> > 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)" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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,
> > > >
> > > >
> > > >
> > > >
> > >
> > >
> > >
>
>
>

Although I noticed that it only works if A1:A10 is numeric, while Bob's works
on both numbers and text.



"JMB" wrote:

> Another variation, also array entered. I got it by piecing together what I
> could remember from one of Harlan's posts.
>
> =SUM(--(FREQUENCY(IF(B1:B10="X",A1:A10,""),IF(B1:B10="X",A1:A10,""))>0))
>
> "Bob Phillips" wrote:
>
> > =SUM(--(FREQUENCY(IF(B1:B10="X",MATCH(A1:A10,A1:A10,0)),ROW(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)" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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)" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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,
> > > > >
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > > >
> >
> >
> >

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)),ROW(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)" <[email protected]> wrote in message
> news:[email protected]...
> > 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)" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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,
> > > >
> > > >
> > > >
> > > >
> > >
> > >
> > >
>
>
>

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)),ROW(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)" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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)" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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,
> > > > >
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > > >
> >
> >
> >

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)),ROW(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)" <[email protected]> wrote in message
> news:[email protected]...
> > 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)" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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,
> > > >
> > > >
> > > >
> > > >
> > >
> > >
> > >
>
>
>

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)),ROW(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)" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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)" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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,
> > > > >
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > > >
> >
> >
> >

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)),ROW(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)" <[email protected]> wrote in message
> news:[email protected]...
> > 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)" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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,
> > > >
> > > >
> > > >
> > > >
> > >
> > >
> > >
>
>
>

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)),ROW(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)" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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)" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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,
> > > > >
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > > >
> >
> >
> >

NG wrap-around caused it I presume?

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Kent (thanks)" <[email protected]> wrote in message
news:[email protected]...
> 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)),ROW(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)" <[email protected]> wrote in
message
> > > news:[email protected]...
> > > > 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)" <[email protected]> wrote in
message
> > > > > news:[email protected]...
> > > > > > 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,
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >

Just a typo on my part, inserted an extra " where I did not need it

"Bob Phillips" wrote:

> NG wrap-around caused it I presume?
>
> --
> HTH
>
> Bob Phillips
>
> (remove xxx from email address if mailing direct)
>
> "Kent (thanks)" <[email protected]> wrote in message
> news:[email protected]...
> > 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)),ROW(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)" <[email protected]> wrote in
> message
> > > > news:[email protected]...
> > > > > 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)" <[email protected]> wrote in
> message
> > > > > > news:[email protected]...
> > > > > > > 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,
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
>
>
>