conditional statements

[JanS]

I need a simpler way COUNT the number of instances in a column in
which the cell value is BETWEEN two other values. For example,
suppose the cells in column 300 rows deep each contain a number
between 0 and 100. How can I count the number of cells in which the
value falls between 75 and 85? So far, the only solution I have found is
to add an extra column with AND(cell>75,cell<85) then count the
"TRUE" results in that column. That's okay if you are only testing one
range but counting values in multiple ranges (35<x<45, 50<x<72,
etc.) is time and space intensive.

I am somewhat competent in Excel but have have not yet learned visual
basic or how to work with Macros.

 

[Harald Staff]

Hi

Two ways among several:
=COUNTIF(A1:A200,">"&75)-COUNTIF(A1:A200,">="&85)
=SUMPRODUCT((A1:A200>75)*(A1:A200<85))

--
HTH. Best wishes Harald
Excel MVP
Followup to newsgroup only please

"JanS" <[email protected]> skrev i melding
I need a simpler way COUNT the number of instances in a column in
which the cell value is BETWEEN two other values. For example,
suppose the cells in column 300 rows deep each contain a number
between 0 and 100. How can I count the number of cells in which the
value falls between 75 and 85? So far, the only solution I have found is
to add an extra column with AND(cell>75,cell<85) then count the
"TRUE" results in that column. That's okay if you are only testing one
range but counting values in multiple ranges (35<x<45, 50<x<72,
etc.) is time and space intensive.

I am somewhat competent in Excel but have have not yet learned visual
basic or how to work with Macros.

 

[JanS]

Thank you, thank you, thank you!!! You just saved me HOURS! I swear,
I've never thought of using the countif function in a formula. Duh.

Jan

 

[Harald Staff]

You're welcome Jan. Thanks for the feedback.

Best wishes Harald
Excel MVP
Followup to newsgroup only please

 

[Dave Smith]

One way:

=SUMPRODUCT((A1:A300>75)*(A1:A300<85)*A1:A300)

-Dave

I need a simpler way COUNT the number of instances in a column in
which the cell value is BETWEEN two other values. For example,
suppose the cells in column 300 rows deep each contain a number
between 0 and 100. How can I count the number of cells in which the
value falls between 75 and 85? So far, the only solution I have found is
to add an extra column with AND(cell>75,cell<85) then count the
"TRUE" results in that column. That's okay if you are only testing one
range but counting values in multiple ranges (35<x<45, 50<x<72,
etc.) is time and space intensive.

I am somewhat competent in Excel but have have not yet learned visual
basic or how to work with Macros.

 

[Janel]

I use the DCOUNT function a lot, however I can never get
it to work when my range is a Microsoft Query. I can
clearly see that there are rows of data matching my
criteria, but I still get a result of zero. Does anyone
have any suggestions on how I can fix this? or if I should
be using a different function? Thanks!