+ Reply to Thread
Results 1 to 7 of 7

Need to count if the difference in values between two arrays is less than 5

  1. #1
    Registered User
    Join Date
    09-07-2012
    Location
    MD, USA
    MS-Off Ver
    Excel:Mac 2011
    Posts
    3

    Need to count if the difference in values between two arrays is less than 5

    I have three columns, which I have simplified for this problem, but they are team, start and finish. They are located in another workbook, so i cannot generate a fourth column to produce the differences between the start and finish.

    I would like to count if the difference between (Finish-Start) is less than 5, and team equals 2.

    In my example worksheet (attached), the answer should be 2, but I can only get that by generating the fourth column "difference" first and using "=COUNTIFS(A3:A22, "=2", D3:D22, "<=5")" (cell D23), which I can't do in my original problem, because I can't generate that column in the new workbook, or original workbook.

    I have tried "=countifs(A3:A22, "=2", b3:b22-c3:c22, "<=5")" but this doesn't work . . . but that may explain better the logic I am trying to introduce into a cell.

    Thank you in advance.
    Attached Files Attached Files

  2. #2
    Forum Expert ConneXionLost's Avatar
    Join Date
    03-11-2009
    Location
    Victoria, Canada
    MS-Off Ver
    2010
    Posts
    2,952

    Re: Need to count if the difference in values between two arrays is less than 5

    Try this:

    Please Login or Register  to view this content.
    Confirmed as an array formula with Ctrl-Shift-Enter.
    Would you like to say thanks? Please click the: " Add Reputation" button, on the grey bar below the post.

  3. #3
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Need to count if the difference in values between two arrays is less than 5

    or this regular formula:

    =SUMPRODUCT((A3:A22=2)*(C3:C22-B3:B22<=5))
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  4. #4
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Need to count if the difference in values between two arrays is less than 5

    italianiceman,

    Welcome to the forum!
    Give this a try:
    Please Login or Register  to view this content.
    Hope that helps,
    ~tigeravatar

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  5. #5
    Banned User!
    Join Date
    10-14-2006
    Posts
    1,211

    Re: Need to count if the difference in values between two arrays is less than 5

    =SUMPRODUCT(--(A3:A22=2),--(C3:C22-B3:B22<=5))

    Just normally Enter

  6. #6
    Registered User
    Join Date
    09-07-2012
    Location
    MD, USA
    MS-Off Ver
    Excel:Mac 2011
    Posts
    3

    Re: Need to count if the difference in values between two arrays is less than 5

    Thank you everyone. I am going to try some of these solutions on my larger file and see if they work. I will be sure to let everyone know.

  7. #7
    Registered User
    Join Date
    09-07-2012
    Location
    MD, USA
    MS-Off Ver
    Excel:Mac 2011
    Posts
    3

    Re: Need to count if the difference in values between two arrays is less than 5

    I ended up using tigeravatar and teethless mama's suggestion. Thank you again for your help.

+ 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