how to use "countif" in excel 5

[CaitlinWhybrow]

Hi

I'm having real trouble using the countif formula in excel. What I want is to count the number of values above a value in another cell
=COUNTIF(b8:b48,">B2&quot

I think excel is treating B2 as text (it's formatted as a number) instead of a number and is returning 0. If I put in ">10" instead, I get the right count. Is there any way to convince excel that B2 is a number?

Is it possible to put in a formula:
=COUNTIF(b8:b48,">0.5*max(b8:b48&quot
That seems to give 0 too.

Thanks!

Cait

 

[Kalechibki]

The best solution I've found to that problem in my own work is to, in another column, say the C column, with an if statement, and then sum that answer. For example (Using the values you gave before), put a formula in C8 stating "=if(B8>B$2,1,0)" and then carry that through C48. Then, in another cell, input "=Sum(C8:C48)".

This should also work, with a little modification, for your other question with a formula.

So far, I've not been able to use a Cell reference in Countif, unless it's byitself, and your just counting the cells with the same value.

 

[carrr]

Actually, you can make a cell reference work like so...

Using your example, =COUNTIF(b8:b48,">"&B2) should work.

 

[Hasit]

Cait, not sure I know how to reference a cell in Countif, or even if it is possible. However, try this formula, which works:

=SUM((A1:A5>B1)*(1))

Make sure after you enter it, you convert it to an array formula, by hitting CRTL-SHIFT-ENTER

HTH.

 

[xlhelp]

To extend Kalechibki's suggestion one step further, what you can use is
=SUM(IF(B8:B48>B3,1,0))
as an array formula. What that means is one you have typed formula above, press =CTRL/SHIFT/ENTER

And also with some modification this formula will work for your other question as well.

 

[CaitlinWhybrow]

Thank you - I've tried 2 out of the 3 ways that have been suggested and they work really well.

This is a great website. Thank you for tkaing the time to give me your help!

 

[TOSNW]

Newbie here: Try this generalisation of the reply from carrr...

1] In any cell enter: =COUNTIF(NUMBERS, ">"&REFERENCE)
2] In another cell enter the threshold number (say) 0.5
3] Name that cell: REFERENCE
4] Fill a (rectangular) range of several cells with: =RAND()
5] Name that range: NUMBERS
6] Watch the (dynamic) result in cell at [1] above whilst...
7] Recalculate the numbers by PRESS-&-HOLD key: F9

It worked for me!