Thanks:  0
Likes:  0

# Thread: Using "CountA" function with a Range

1. Hello. I would like to get a count of all items in a column which fall between a range. For instance, how many items cost more than \$5 and less than \$10. Can anyone help me out? Many thanks.

2. for A1:A10, you could use
=COUNTIF(A1:A10,">"&5)-COUNTIF(A1:A10,">="&10)

3. On 2002-05-03 07:32, IML wrote:
for A1:A10, you could use
=COUNTIF(A1:A10,">"&5)-COUNTIF(A1:A10,">="&10)
Nice one IML,

quick query : ">5" seems to work in place of ">"&5

(although not in your second arguement, which is correct)

do you default to this just out of habit, or is there a subtelty I'm missing ?

thanks
Chris

4. On 2002-05-03 12:45, Chris Davison wrote:
On 2002-05-03 07:32, IML wrote:
for A1:A10, you could use
=COUNTIF(A1:A10,">"&5)-COUNTIF(A1:A10,">="&10)
Nice one IML,

Chris

(although not in your second arguement, which is correct)

do you default to this just out of habit, or is there a subtelty I'm missing ?

thanks

Thanks Chris,
I think

=COUNTIF(A1:A10,">5")-COUNTIF(A1:A10,">=10")

would be considered more correct. The amperstand is a bad habit since I'm use to tying it to cells, as opposed to constants.
quick query : ">5" seems to work in place of ">"&5

5. sounds like my "indirects" where everything is inside quotes until the variables....

6. On 2002-05-03 13:04, Chris Davison wrote:
sounds like my "indirects" where everything is inside quotes until the variables....

The use of & in COUNTIF and SUMIF helps to distinguish between constants and variables. Ian's "habit", that is, ...&a-constant has a good side-effect:

When you have the following in a French or Dutch version of Excel (Excel 97, anyway):

=COUNTIF(A1:A10,">10,5")

don't try to send your WB to an American with the US version of Excel. However,

=COUNTIF(A1:A10,">"&10,5)

will give the American no problem.

interesting !

Any insight as to why Dutch or French syntaxes are different ?

rephrase : are both syntaxes acceptable to Excel, just that Netherlands and France prefer option (a) for some reason

or is Excel actually coded differently to take account of preferences in syntax by Netherland and France ?

.... tangent : is this just Excel ? Or is it Dutch and French computing in general ? What happens when France wants to launch a rocket to the International Space Station ? is there lots of "computer translations" ?

sorry...silly questions
interesting topic though

8. On 2002-05-03 15:35, Chris Davison wrote:

interesting !

Any insight as to why Dutch or French syntaxes are different ?

rephrase : are both syntaxes acceptable to Excel, just that Netherlands and France prefer option (a) for some reason

or is Excel actually coded differently to take account of preferences in syntax by Netherland and France ?

.... tangent : is this just Excel ? Or is it Dutch and French computing in general ? What happens when France wants to launch a rocket to the International Space Station ? is there lots of "computer translations" ?

sorry...silly questions
interesting topic though

[img]/board/images/smiles/icon_smile.gif[/img]

I think it's just a bug in Excel's parser itself (Excel 97).

A Dutch WB can be immediately opened with the American version. All function names will change immediately to their English version. But, this process does not seem to look at antthing between double quotes. So you get from:

=AANTAL.ARG(A1:A10,">1,6")

=COUNTIF(A1:A10,">1.6")

I've seen this happen at school (all English) with files set up in the Dutch version.

Can anyone verify this?

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•