As an example I have numerical data in cells A1:A5 (12,0,1,7,2). In cell B1 I
have the number 3. Now in cell A6 I want to count the number of cells that
are greater than 3. The answer should be 2. The countif formula lets me put
=countif(A1:A5,3) but I want to have linked it cell B1 so I can change
the parameter. I am trying to do this with the Indirect function but I can
not figure it out. Thanks!

=countif(A1:A5,B1)

"Doug Laidlaw" wrote:

As an example I have numerical data in cells A1:A5 (12,0,1,7,2). In cell B1 I
have the number 3. Now in cell A6 I want to count the number of cells that
are greater than 3. The answer should be 2. The countif formula lets me put
=countif(A1:A5,3) but I want to have linked it cell B1 so I can change
the parameter. I am trying to do this with the Indirect function but I can
not figure it out. Thanks!

I would have expected something like that to work too, but it doesn't for
me.
Instead, try:

=SUMPRODUCT(--(A1:A5B1))

HTH,
Paul

--

"Sean Timmons" wrote in message
...
=countif(A1:A5,B1)

"Doug Laidlaw" wrote:

As an example I have numerical data in cells A1:A5 (12,0,1,7,2). In cell
B1 I
have the number 3. Now in cell A6 I want to count the number of cells
that
are greater than 3. The answer should be 2. The countif formula lets me
put
=countif(A1:A5,3) but I want to have linked it cell B1 so I can
change
the parameter. I am trying to do this with the Indirect function but I
can
not figure it out. Thanks!

=countif(A1:A5,"" & B1)


"PCLIVE" wrote:

I would have expected something like that to work too, but it doesn't for
me.
Instead, try:

=SUMPRODUCT(--(A1:A5B1))

HTH,
Paul

--

"Sean Timmons" wrote in message
...
=countif(A1:A5,B1)

"Doug Laidlaw" wrote:

As an example I have numerical data in cells A1:A5 (12,0,1,7,2). In cell
B1 I
have the number 3. Now in cell A6 I want to count the number of cells
that
are greater than 3. The answer should be 2. The countif formula lets me
put
=countif(A1:A5,3) but I want to have linked it cell B1 so I can
change
the parameter. I am trying to do this with the Indirect function but I
can
not figure it out. Thanks!

Yes! I was trying every option under the sun by moving the quotation marks "
.. I knew it was a simple but I couldn't get it.


PCLIVE earlier helped me with the =SUMPRODUCT(--(A1:A5B1)). What is the --
part of the formula doing? Your answer works, but I don't know how!

Thank you both,
Doug

"Toppers" wrote:

=countif(A1:A5,"" & B1)


"PCLIVE" wrote:

I would have expected something like that to work too, but it doesn't for
me.
Instead, try:

=SUMPRODUCT(--(A1:A5B1))

HTH,
Paul

--

"Sean Timmons" wrote in message
...
=countif(A1:A5,B1)

"Doug Laidlaw" wrote:

As an example I have numerical data in cells A1:A5 (12,0,1,7,2). In cell
B1 I
have the number 3. Now in cell A6 I want to count the number of cells
that
are greater than 3. The answer should be 2. The countif formula lets me
put
=countif(A1:A5,3) but I want to have linked it cell B1 so I can
change
the parameter. I am trying to do this with the Indirect function but I
can
not figure it out. Thanks!

Without the "--", the results of matching the items in A1:A5 will be either
True or False in which case the final result is "0". Using "--" converts
the True and False to ones (1) or zeros (0). SUMPRODUCT is normally useful
to get a count of items that match certain, multiple criteria from the same
row or even multiple rows.
Though the SUMPRODUCT formula gives the correct answer, I think the COUNTIF
formula provided by Toppers is way to go.

Toppers,
I thought I had tried the COUNTIF formula the way you did and it returned an
error. Obviously I was mistaken as it works now.
Thanks.

--

"Doug Laidlaw" wrote in message
...
Yes! I was trying every option under the sun by moving the quotation marks
"
. I knew it was a simple but I couldn't get it.


PCLIVE earlier helped me with the =SUMPRODUCT(--(A1:A5B1)). What is
the --
part of the formula doing? Your answer works, but I don't know how!

Thank you both,
Doug

"Toppers" wrote:

=countif(A1:A5,"" & B1)


"PCLIVE" wrote:

I would have expected something like that to work too, but it doesn't
for
me.
Instead, try:

=SUMPRODUCT(--(A1:A5B1))

HTH,
Paul

--

"Sean Timmons" wrote in message
...
=countif(A1:A5,B1)

"Doug Laidlaw" wrote:

As an example I have numerical data in cells A1:A5 (12,0,1,7,2). In
cell
B1 I
have the number 3. Now in cell A6 I want to count the number of
cells
that
are greater than 3. The answer should be 2. The countif formula
lets me
put
=countif(A1:A5,3) but I want to have linked it cell B1 so I can
change
the parameter. I am trying to do this with the Indirect function but
I
can
not figure it out. Thanks!

=COUNTIF(A1:A5,""&B1)

On Aug 8, 12:39 pm, "PCLIVE" wrote:
Without the "--", the results of matching the items in A1:A5 will be either
True or False in which case the final result is "0". Using "--" converts
the True and False to ones (1) or zeros (0). SUMPRODUCT is normally useful
to get a count of items that match certain, multiple criteria from the same
row or even multiple rows.
Though the SUMPRODUCT formula gives the correct answer, I think the COUNTIF
formula provided by Toppers is way to go.

Toppers,
I thought I had tried the COUNTIF formula the way you did and it returned an
error. Obviously I was mistaken as it works now.
Thanks.

--

"Doug Laidlaw" wrote in message

...



Yes! I was trying every option under the sun by moving the quotation marks
"
. I knew it was a simple but I couldn't get it.

PCLIVE earlier helped me with the =SUMPRODUCT(--(A1:A5B1)). What is
the --
part of the formula doing? Your answer works, but I don't know how!

Thank you both,
Doug

"Toppers" wrote:

=countif(A1:A5,"" & B1)

"PCLIVE" wrote:

I would have expected something like that to work too, but it doesn't
for
me.
Instead, try:

=SUMPRODUCT(--(A1:A5B1))

HTH,
Paul

--

"Sean Timmons" wrote in message
...
=countif(A1:A5,B1)

"Doug Laidlaw" wrote:

As an example I have numerical data in cells A1:A5 (12,0,1,7,2). In
cell
B1 I
have the number 3. Now in cell A6 I want to count the number of
cells
that
are greater than 3. The answer should be 2. The countif formula
lets me
put
=countif(A1:A5,3) but I want to have linked it cell B1 so I can
change
the parameter. I am trying to do this with the Indirect function but
I
can
not figure it out. Thanks!- Hide quoted text -

- Show quoted text -