+ Reply to Thread
Results 1 to 9 of 9

Find order number and count if between range

  1. #1
    Registered User
    Join Date
    08-10-2013
    Location
    washington
    MS-Off Ver
    Excel 2003
    Posts
    5

    Find order number and count if between range

    I have a spreadsheet setup that records mistakes on orders. In one column it has the order number of the mistake as it is logged on a second sheet I have the order ranges that have gone out. I need a forumla that will check the column that has the order number for the mistake order and see if it is between the order ranges on the other sheet then count how many mistakes there are.

    Example:
    Sheet1
    A1
    123456
    123457
    123458
    123459
    123460

    Sheet 2
    A1 B1
    123450 123456
    123457 123459

    So the output from the formula would show 4

    Any help you can give would be much appreciated. Also this is in google spreadsheets but I figure the formula should be pretty similar.
    Last edited by sonata7245; 08-10-2013 at 06:15 PM.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,384

    Re: Find order number and count if between range

    Thanks for the title change

    Your profile says 2003, so use this, copied down...
    =SUMPRODUCT(($A$4:$A$8>=A12)*($A$4:$A$8<=B12))

    If you do have 2007 or later, use this simpler version...
    =COUNTIFS($A$4:$A$8,">="&A12,$A$4:$A$8,"<="&B12)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    08-10-2013
    Location
    washington
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Find order number and count if between range

    I tired the sumproduct function and it gives me the following error: error: Range has no entry corresponding to this cell

    I changed it just a little to fit what I am trying to do:
    =SUMPRODUCT((Sheet1!$A$5:$A$5980>=$A$3:$A1000)*(Sheet1!$A$5:$A$5980<=$B$3:$B$1000))

    I probably messed up somewhere. A little more info:
    Sheet 1 column A has the order numbers that we log as errors are reported to us.
    Sheet 2 has Column A and Column B
    Column A is the beginning of the order range
    Column B is the end of the order range

    Hope that helps. Countifs does not work in google spreadsheets.

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,384

    Re: Find order number and count if between range

    why are you using a range for the >= part?

    From your sample, it looked like you wanted to count for each range?

  5. #5
    Registered User
    Join Date
    08-10-2013
    Location
    washington
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Find order number and count if between range

    There are multiple ranges being entered over time. If its not possible to have just one formula to do a total count I can input it manually then do a sum.

  6. #6
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Find order number and count if between range

    Countifs does not work in google spreadsheets.?????????? why you asking in Excel Formulas & Functions then?
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  7. #7
    Registered User
    Join Date
    08-10-2013
    Location
    washington
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Find order number and count if between range

    I figured they were similar enough programs that there may be something close I can work with. I did get the sumproduct formula to work for a a specific order range but it returned a zero value.

  8. #8
    Registered User
    Join Date
    08-10-2013
    Location
    washington
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Find order number and count if between range

    Never mind its working now. Thank you so much!

  9. #9
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,384

    Re: Find order number and count if between range

    Happy to help

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. index function error using match function to get data from a cell in an array
    By mabildgaard in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-17-2013, 02:21 AM
  2. [SOLVED] Using Offset function as the array in the PercentRank function is giving wrong result
    By Bobneil in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 08-06-2013, 09:29 PM
  3. Replies: 1
    Last Post: 03-21-2012, 11:22 AM
  4. Replies: 2
    Last Post: 03-20-2009, 01:29 PM
  5. [SOLVED] Excel - User Defined Function Error: This function takes no argume
    By BruceInCalgary in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-18-2006, 04:05 PM

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