Count if within range

[secondtry]

Hi all, I can't seem to figure out how to manipulate the count if function to achieve the right output.

I have a column (say column A) that lists different numbers from 0 to 100 (let's say 50 rows for this example). What I was hoping to do was return the number of rows that fall between 1 and 10, 11 and 20, 21 and 30......91 and 100 in one cell formula.

Any suggestions?

Thanks in advance.

 

[VoG]

Try like this

=COUNTIF(A:A,">=1")-COUNTIF(A:A,">10")

 

[secondtry]

Brilliant! Thank you for your help VoG.

 

[secondtry]

I wonder if I could take it a step further and have the formula ignore those instances where column B has an "X"?

For example:
A B
5
8 X
21
15
30
7

COUNTIF column A where >1 and <10 AND where no "X"

 

[VoG]

Try

=SUMPRODUCT(--(A1:A100>=1),--(A1:100<11),--(B1:B100<>"X"))

 

[T. Valko]

I wonder if I could take it a step further and have the formula ignore those instances where column B has an "X"?

For example:
A B
5
8 X
21
15
30
7

COUNTIF column A where >1 and <10 AND where no "X"

Do you mean: >=1 and <=10 ?

Are there any zeros or negative numbers in the range?

What version of Excel are you using?

 

[secondtry]

Do you mean: >=1 and <=10 ?

Are there any zeros or negative numbers in the range?

What version of Excel are you using?

Thank you for clarifying T. Valko, I did mean >=1 and <=10. To clarify for anyone who might check out this post there were no negative numbers in the range but there were blanks. I am using Excel 2003.

 

[secondtry]

Try

=SUMPRODUCT(--(A1:A100>=1),--(A1:100<11),--(B1:B100<>"X"))

This worked. Thanks again VoG. This was a huge help and I learned a few new things that I'll be able to apply to future spreadsheets.