Help Needed with simplifying

[gdrowell]

Is there a way to use the Countif function (or any function) to look up a range of criteria?

Example:

If i had a range of numbers (with repeating values), I would want to know How many times a range of numbers appear.

Right now I use =COUNTIF(A1:A100, "20"), if i wanted to see how many times "20" appears.

What i am looking for is a Function equation that can lookup multiple numbers in a range, then you the COUNT function to give me a total number of appearance ( i.e. How many time numbers 20-50, appear in the range of numbers)


Any help would be appreciated

 

[Peter_SSs]

If you have the COUNTIFS function in your Excel version then

=COUNTIFS(A1:A100,">=20",A1:A100,"<=50")


Otherwise

=SUMPRODUCT(--(A1:A100>=20),--(A1:A100<=50))


or you could use a double COUNTIF like this

=COUNTIF(A1:A100,"<=50")-COUNTIF(A1:A100,"<20")

 

[footoo]

=countif(a1:a100,">19")-countif(a1:a100,">50")

 

[Peter_SSs]

=countif(a1:a100,">19")-countif(a1:a100,">50")

Obviously we posted at the same time but this would include numbers like 19.2 (if fractional numbers are possible with the OP's data) in the 20-50 range so to be safe it would need to be
=COUNTIF(A1:A100,">=20")-COUNTIF(A1:A100,">50")

 

[footoo]

Thanks for correcting.

 

[gdrowell]

Thank you all for your assistance. Can I do this , looking for random numbers in the Criteria? (ie, I only want to count how many times certain numbers ( ex: 7, 8, 15, 26, 33, 44, etc ) appear)

 

[gdrowell]

=COUNTIF($F$2:$F$11,A9) + COUNTIF($F$2:$F$11,A10) + COUNTIF($F$2:$F$11,A11)

Above is what i came up with (using Mr. Excel MVP mulitple countif). But i want don't want to have to do that for a whole section of Column A (i.e A9 to A11)

 

[Peter_SSs]

I only want to count how many times certain numbers ( ex: 7, 8, 15, 26, 33, 44, etc ) appear

Try

Excel Workbook
	A	B	C	D	E	F
1
2						7
3						5
4						8
5						9
6	4					6
7						2
8						9
9	1					8
10	8					1
11	2					7
Counting

 

[gdrowell]

Thank You. This works perfectly.

 

[Peter_SSs]

Thank You. This works perfectly.

You're welcome.