Count if Formula 1

[Jamie2002]

I'm currently using a countif formula which has the condition as eg ">50"

This returns a count of anything in a list greater than 50.

What I need though is to have this 50 as a variable. I have tried a cell reference but Excel is then looking for the cell ref in the list ie ">F27"

I though about using a macro button with code that concatenated the first half of the formula then took the variable and pieced it together and then put this in the cell as a string which would then work, but this does'nt seem to be working. Below is the code......

'
' Macro1 Macro
' Macro recorded 18/10/2002 by Finance
'
Dim Limit As Integer

Limit = Range("g29&quot



Range("F35&quot.Select
ActiveCell.FormulaR1C1 = "=COUNTIF(Range(F12:F24)," & ">" & string(Limit)&&quot"

End Sub



Can anyone help...?

Thanks

Jamie

 

[xlbo]

No need for code:
In B1, where you have a list of numbers in A1:A1000
=COUNTIF(A1:A1000,">" & B2)

where your number to be bigger than is in B2

HTH Rgds
~Geoff~

 

[aMember]

But if you needed to do it through code, it would be

Dim Limit As Range
Dim CountCol As Range
Set Limit = Range("B1&quot
Set CountCol = Range("$A$1", "$A$10&quot

formulaString = "=COUNTIF(" & CountCol.Address & "," & """>" & Limit.Value & """" & &quot"
ActiveCell.Formula = formulaString