Back to Forms in Excel VBA archive index

Back to archive home

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

Geoff Branch

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

=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

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

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.

=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

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

This archive is from the original message board at www.MrExcel.com.

All contents © 1998-2004 MrExcel.com.

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.

All contents © 1998-2004 MrExcel.com.

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.