Thanks:  0
Likes:  0

1. I have a series of numbers in cell B1 thru B26. Is there a way to count the numbers in this range that are >1 and <11 and have this number show up in cell B27?
The count if function will only work with one criteria.
Any suggestions?

Jerry

2. On 2002-04-15 17:43, jrussell wrote:
I have a series of numbers in cell B1 thru B26. Is there a way to count the numbers in this range that are >1 and <11 and have this number show up in cell B27?
The count if function will only work with one criteria.
Any suggestions?

Jerry
in B27 put

=COUNTIF(B1:B26,"<11")-COUNTIF(B1:B26,"<=1")

3. I applied your suggestion and modified it a bit.
The following works great.

Thanks for the help.

=COUNTIF(B1:B26,">0")-COUNTIF(B1:B26,">11")

Jerry

4. Try this:

=SUM(IF(A1:A26<11,IF(A1:A26>1,1,0),0))

This is an array formula and must be enter be pressing Ctrl-Alt-Enter together. When enter correctly Excel will put {} around the formula to identify it as an array.

5. On 2002-04-15 18:15, Ricky Morris wrote:
Try this:

=SUM(IF(A1:A26<11,IF(A1:A26>1,1,0),0))

This is an array formula and must be enter be pressing Ctrl-Alt-Enter together. When enter correctly Excel will put {} around the formula to identify it as an array.

i think you mean ctrl+shift+enter?

[ This Message was edited by: anno on 2002-04-15 19:09 ]

[ This Message was edited by: anno on 2002-04-15 19:12 ]

6. In the reply from Yogi Anand,
Instead of choosing the range <11, <=1 , suppose I have 100 (say) in a cell,, can I refer the conditions to this cell? e.g

7. In the reply from Yogi Anand,
Instead of choosing the range <11, <=1 , suppose I have 100 (say) in a cell,, can I refer the conditions to this cell? e.g

8. Hi Chaju:
I don't see why not! ... You see what I did was first counted all the cells that were less than 11 (this would cover if a cell were valued at 100; then from this count, I deducted the count of cells that were valued at 1 or less (meaning 0)

9. Hi! I understand the formula is ok.. but however i have 100 in cell E1 and change the formula to the bottom shown:

COUNTIF(\$C\$4:\$M\$35,"
The result shows up 0, which is incorrect!
I want to count withing range -1000 Pls advise, thanks!

10. On 2002-04-15 19:52, chaju wrote:
Hi! I understand the formula is ok.. but however i have 100 in cell E1 and change the formula to the bottom shown:

COUNTIF(\$C\$4:\$M\$35,"
The result shows up 0, which is incorrect!
I want to count withing range -1000 Pls advise, thanks!
Please post part of your list and your counting criteria ... nd then let us take it from there!

## 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
•