MrExcel Publishing
Your One Stop for Excel Tips & Solutions

countif reference to cell

Posted by Geoff Branch on May 07, 2001 10:00 AM

I am trying to use countif where the criteria needs to be an absolute reference to a cell. Anyone able to help? Thanks.
Geoff Branch

Posted by IML on May 07, 2001 10:17 AM

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

Good luck

Posted by Geoff Branch on May 07, 2001 10:35 AM

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

Posted by Aladin on May 07, 2001 10:48 AM

Ian is right...


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.

Etc., etc.

Hope this helps.


Posted by Geoff Branch on May 07, 2001 11:58 AM

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

Posted by Aladin Akyurek on May 07, 2001 12:18 PM

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


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.