Using "CountA" function with a Range

[pzamory]

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.

[IML]

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

[Chris Davison]

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

[IML]

On 2002-05-03 12:45, Chris Davison wrote:

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

[Chris Davison]

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

your "habit" still gives the same answer, I should add, just in case the reader thinks it's a no-no

[Aladin Akyurek]

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

your "habit" still gives the same answer, I should add, just in case the reader thinks it's a no-no

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.



Aladin

[Chris Davison]

Aladin,

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

[Aladin Akyurek]

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

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") instead of:

=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?

Aladin