+ Reply to Thread
Results 1 to 13 of 13

Lookup and count number of times a number is present???

  1. #1
    Forum Contributor
    Join Date
    08-07-2012
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    202

    Lookup and count number of times a number is present???

    What i am looking to do is, say i have three sections of a chart. In these sections there are numbers. Is it possible to use vlookup and have it go thru the three sections and count the number of times 0 is in the sections? Or would i need to use something else? Or is this possible. I just want a cell that will search these sections and give me a total of how many times 0 is there. I don't know if vlookup is what i need or maybe a sumif? Any ideas anyone? I have never used nor wrote any vlookups, so i don't really know what to do with that. I have only done simple sumifs also, so i don't really know how i would do that either. I'm not looking for a sum total, but a number of instances.

    Thanks again guys!!! I do hate to keep bugging you guys with this stuff. I am trying before looking around for help.
    Thanks a bunch.

  2. #2
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Lookup and count number of times a number is present???

    Have you tried >> =COUNTIF($A$1:$C$6,0)?
    HTH
    Regards, Jeff

  3. #3
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Lookup and count number of times a number is present???

    If there are no negative numbers you can do something like this:

    =FREQUENCY((A1:B5,E1:F5,I1:J5),0)

    Where the 3 areas are:

    A1:B5
    E1:F5
    I1:J5
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  4. #4
    Forum Contributor
    Join Date
    08-07-2012
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    202

    Re: Lookup and count number of times a number is present???

    I didn't even think about that. Still don't know much about excel. But that works perfect. Amazing, Amazing, Amazing!!! That's what this excel is and you guys for sharing your knowledge and help!!! Thank you so much!!!

  5. #5
    Forum Contributor
    Join Date
    08-07-2012
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    202

    Re: Lookup and count number of times a number is present???

    That works also. I don't think i will ever get to know all this can do. There are so many ways this can do things. I very much do appreciate all your help though. Very much!!! The more i learn with it the more amazed at it i am. And amazed at the patience you guys have with helping everyone. You guys are just awesome!!!

  6. #6
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Lookup and count number of times a number is present???

    Quote Originally Posted by greenfox74 View Post
    That works also. I don't think i will ever get to know all this can do.
    And neither will I. Tony can work some incredible magic with those formulas.

    Glad you have something to work with now.

  7. #7
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Lookup and count number of times a number is present???

    You're welcome. Thanks for the feedback!

  8. #8
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Lookup and count number of times a number is present???

    Quote Originally Posted by greenfox74 View Post
    That works also. I don't think i will ever get to know all this can do. There are so many ways this can do things.
    You seem eager to learn so if you don't mind I'll explain how that works.

    =FREQUENCY((A1:B5,E1:F5,I1:J5),0)

    The FREQUENCY function returns an array of "Count Ifs" based on the bins:

    =FREQUENCY(range,bins)

    In my formula the bins contains a single value, 0.

    The first bin gets counted as:

    Count If the range is less than or equal to the first bins value which would be the equivalent of:

    =COUNTIF(range,"<=0")

    Since you have 3 different ranges the equivalent COUNTIF would be:

    =COUNTIF(range1,"<=0")+COUNTIF(range2,"<=0")+COUNTIF(range3,"<=0")

    The FREQUENCY function is one of a few functions that can handle multiple area references. When using multiple area references we enclose them in parentheses like this:

    =FREQUENCY((range1,range2,range3),0)

    So, both of these formulas will do the exact same thing:

    =FREQUENCY((A1:B5,E1:F5,I1:J5),0)

    =COUNTIF(A1:B5,"<=0")+COUNTIF(E1:F5,"<=0")+COUNTIF(I1:J5,"<=0")

    Since I don't like to type more than I have to I'll choose to use the shortest formula that does the job.

    When there are multiple bins in the FREQUENCY function:

    =FREQUENCY((A1:B5,E1:F5,I1:J5),{0,5,10})

    Then the array of "Count Ifs" gets evaluated as such:

    Count if range <=0
    Count if range >0 and <=5
    Count if range >5 and <=10
    Count if range >10

    The FREQUENCY function will return one more count than the number of elements in the bins. As you can see above, we have 3 elements in the bins: 0, 5, 10, but the function retruns an array of 4 counts.

    If we only want to pull out a specific count we can use the INDEX function:

    =INDEX(FREQUENCY((A1:B5,E1:F5,I1:J5),{0,5,10}),2)

    That will return the count of bin #2 or:

    Count if range >0 and <=5
    Last edited by Tony Valko; 12-31-2012 at 10:14 AM.

  9. #9
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Lookup and count number of times a number is present???

    Hi Tony,

    Great explanation...

    I was looking at the Excel help for the Frequency function and it shows needing Ctrl + Shift + Enter.

    How come it is not needed here?

  10. #10
    Registered User
    Join Date
    11-16-2012
    Location
    New York
    MS-Off Ver
    Excel 2007
    Posts
    33

    Re: Lookup and count number of times a number is present???

    You're not looking to get an array returned if you're only using a single value. You only need the array formula format if you want FREQUENCY to return multiple frequency values at once.

  11. #11
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Lookup and count number of times a number is present???

    You only need to array enter if you want the results of all the counts.

    For example:

    =FREQUENCY((A1:B5,E1:F5,I1:J5),{0,5,10})

    If we want all the counts we would select a 1 dimensional vertical range of 4 cells then we would array enter the formula in those 4 cells. For example, A10:A13.

    In my original formula:

    =FREQUENCY((A1:B5,E1:F5,I1:J5),0)

    We still get an array of counts. In this case there would be 2 counts:

    Count if range <=0
    Count if range >0

    Since we are only interested in the first count we can just normally enter the formula in a sinlge cell and it will return the first array element or the first count if.

  12. #12
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Lookup and count number of times a number is present???

    Great, thanks for the explanation.

  13. #13
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Lookup and count number of times a number is present???

    You're welcome!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1