Countif indirect question

[Guest]

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!

 

[Guest]

=countif(A1:A5,>B1)

 

[PCLIVE]

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

=SUMPRODUCT(--(A1:A5>B1))

HTH,
Paul

--

 

[Guest]

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

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

=SUMPRODUCT(--(A1:A5>B1))

HTH,
Paul

 

[Guest]

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:A5>B1)). What is the --
part of the formula doing? Your answer works, but I don't know how!

Thank you both,
Doug

 

[PCLIVE]

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.

--

 

[iliace]

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

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.

--









- Show quoted text -