# Thread: Help Needed with simplifying Thanks: 0 Likes: 0

1. ## Help Needed with simplifying

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  Reply With Quote

2. ## Re: Help Needed with simplifying

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")  Reply With Quote

3. ## Re: Help Needed with simplifying

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

4. ## Re: Help Needed with simplifying Originally Posted by footoo =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")  Reply With Quote

5. ## Re: Help Needed with simplifying

Thanks for correcting.  Reply With Quote

6. ## Re: Help Needed with simplifying

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)  Reply With Quote

7. ## Re: Help Needed with simplifying

=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)  Reply With Quote

8. ## Re: Help Needed with simplifying Originally Posted by gdrowell I only want to count how many times certain numbers ( ex: 7, 8, 15, 26, 33, 44, etc ) appear
Try

Counting

 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

 Cell Formula A6 =SUMPRODUCT(--ISNUMBER(MATCH(F2:F11,A9:A11,0)))

Excel tables to the web >> Excel Jeanie HTML 4  Reply With Quote

9. ## Re: Help Needed with simplifying

Thank You. This works perfectly.      Reply With Quote

10. ## Re: Help Needed with simplifying Originally Posted by gdrowell Thank You. This works perfectly.    You're welcome.  Reply With Quote

## User Tag List

#### Tags for this Thread

equation, function, numbers, range, times #### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•