Archive of Mr Excel Message Board

Back to Forms in Excel VBA archive index
Back to archive home

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

Check out our Excel Resources

Re: countif reference to cell

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

Re: countif reference to cell

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

Ian is right...

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

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.


Re: Ian is right...

Posted by Geoff Branch on May 07, 2001 11:58 AM
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...

Posted by Aladin Akyurek on May 07, 2001 12:18 PM
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.


This archive is from the original message board at
All contents © 1998-2004
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.