Help Needed with simplifying
Page 1 of 3 123 LastLast
Results 1 to 10 of 25

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

  1. #1
    New Member
    Join Date
    Dec 2017
    Posts
    32
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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

  2. #2
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,483
    Post Thanks / Like
    Mentioned
    86 Post(s)
    Tagged
    19 Thread(s)

    Default 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")
    Last edited by Peter_SSs; Aug 25th, 2019 at 01:34 AM.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  3. #3
    Board Regular
    Join Date
    Sep 2016
    Posts
    2,566
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Help Needed with simplifying

    =countif(a1:a100,">19")-countif(a1:a100,">50")
    Last edited by footoo; Aug 25th, 2019 at 01:33 AM.

  4. #4
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,483
    Post Thanks / Like
    Mentioned
    86 Post(s)
    Tagged
    19 Thread(s)

    Default Re: Help Needed with simplifying

    Quote Originally Posted by footoo View Post
    =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")
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  5. #5
    Board Regular
    Join Date
    Sep 2016
    Posts
    2,566
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Help Needed with simplifying

    Thanks for correcting.

  6. #6
    New Member
    Join Date
    Dec 2017
    Posts
    32
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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)

  7. #7
    New Member
    Join Date
    Dec 2017
    Posts
    32
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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)

  8. #8
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,483
    Post Thanks / Like
    Mentioned
    86 Post(s)
    Tagged
    19 Thread(s)

    Default Re: Help Needed with simplifying

    Quote Originally Posted by gdrowell View Post
    I only want to count how many times certain numbers ( ex: 7, 8, 15, 26, 33, 44, etc ) appear
    Try

    Counting

    ABCDEF
    1
    2 7
    3 5
    4 8
    5 9
    64 6
    7 2
    8 9
    91 8
    108 1
    112 7

    Spreadsheet Formulas
    CellFormula
    A6=SUMPRODUCT(--ISNUMBER(MATCH(F2:F11,A9:A11,0)))


    Excel tables to the web >> Excel Jeanie HTML 4
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  9. #9
    New Member
    Join Date
    Dec 2017
    Posts
    32
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help Needed with simplifying

    Thank You. This works perfectly.

  10. #10
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,483
    Post Thanks / Like
    Mentioned
    86 Post(s)
    Tagged
    19 Thread(s)

    Default Re: Help Needed with simplifying

    Quote Originally Posted by gdrowell View Post
    Thank You. This works perfectly.
    You're welcome.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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