countif reference to cell

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
=COUNTIF(yourrange,$a$1)

Good luck

That's just my point!!! It doesn't work!!! It seems that you have to enter a value, not a cell reference.
Geoff Branch

Ian is right...

Geoff,

Here a sample data

{1;2;5;2;2}

that occupies A1:A5.

In B1 enter: 2

To count how many 2's are in A:A5, you can just use

=COUNTIF(A1:A5,$B$1)

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.

Etc., etc.

Hope this helps.

Aladin

Re: Ian is right...

Thanks to all you helped! It's a syntax thing.
=COUNTIF(E362:E368,">"&$D$373) works! But what does the "" and & have to do with it all? Probably a newbie question, but I can't help but asking. Again, many, many thanks.

Geoff Branch

How a formula with COUNTIF is evaluated...

COUNTIF requires a constant value as its last argument. And this argument must be put within a pair of double quotes.

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

=COUNTIF(range,">4").

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.

Aladin