Using "CountA" function with a Range

pzamory

Board Regular
Joined
May 2, 2002
Messages
135
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.
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
for A1:A10, you could use
=COUNTIF(A1:A10,">"&5)-COUNTIF(A1:A10,">="&10)
 
Upvote 0
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
:)
 
Upvote 0
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
 
Upvote 0
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

:)
 
Upvote 0
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.

:biggrin:

Aladin
 
Upvote 0
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

:)
 
Upvote 0
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

/board/images/smiles/icon_smile.gif

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
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top