|Check out our Excel Resources|
I may be missing something, but it should work fine just putting dollar signs aroung your criteria cell. Ie count the number of occurances of what is in cell one use
That's just my point!!! It doesn't work!!! It seems that you have to enter a value, not a cell reference.
Here a sample data
that occupies A1:A5.
In B1 enter: 2
To count how many 2's are in A:A5, you can just use
Enter this formula, say in C1, you'll get 3 as result.
In principle, it's better to put a condition (value to be counted) in a cell of its own. You can the have the following possibilities:
=COUNTIF(range,cell-ref) => count values that are equal to the value of cell-ref.
=COUNTIF(range,">"&cell-ref) => count values that are larger than the value of cell-ref.
Hope this helps.
In case you use COUNTIF(range,"=2") or COUNTIF(range,"="&B1), you can omit "=", because this is the default case.
When you want to use a relational operator other than = (e.g., <>,>, etc), you need to have the double quotes around the operator and the value, like in
The & in =COUNTIF(range,">"&B1) is evaluated first: the value in B1 is fetched and concatenated with ">". Therafter the formula that now contains a constant together with a rel operator in double quotes gets evaluated.