Results 1 to 9 of 9

How to count occurrences of a comparison within an array

Threaded View

  1. #1
    Registered User
    Join Date
    03-27-2014
    Location
    Las Vegas, Nevada
    MS-Off Ver
    Excel 2013
    Posts
    12

    How to count occurrences of a comparison within an array

    I have two columns of dates that look something like this:

    (Column A (Column B)
    Order Received Order Processed
    02/14/2014 02/14/2014
    02/15/2014 02/25/2014
    n/a
    02/16/2014 canceled
    02/16/2014 02/17/2014
    n/a n/a
    02/17/2014 02/28/2014

    I'm looking for a formula that will tell me how many times the date in the first column is 5 days or less than the date in the second column. In other words, count how many times column B minus column A is less than or equal to five for the array (i.e. B-A<=5).

    I have tried several formulas without success, usually because the cells with text in them throw the formula(s) off.

    Here are some examples of formulas that didn't work:

    =SUMPRODUCT(--ISNUMBER('2014'!$E$2:$E$1001),--ISNUMBER('2014'!$F$2:$F$1001),--('2014'!$F$2:$F$1001-'2014'!$E$2:$E$1001<=5))

    {=COUNTIF('2014'!$F$2:$F$1001,('2014'!$F$2:$F1001)-('2014'!$E$2:$E$1001)<=5)}

    =SUMPRODUCT(--(N('2014'!$F$2:$F$1001)-N('2014'!$E$2:$E$1001)<=5))

    {=IF(ISNUMBER('2014'!$F:$F),IF(ISNUMBER('2014'!$E:$E),SUMPRODUCT(--('2014'!$F:$F-'2014'!$E:$E<=5))))}
    Last edited by jbh001; 03-27-2014 at 06:32 PM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Count number of occurrences but if there are consecutive matches count them as one
    By michaljireht in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-26-2013, 08:26 PM
  2. Replies: 8
    Last Post: 05-15-2013, 04:50 PM
  3. Replies: 3
    Last Post: 01-09-2013, 07:55 PM
  4. Count occurrences in an array
    By stavdav in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 03-16-2010, 08:28 AM
  5. count Re-occurrences
    By stevekirk in forum Excel General
    Replies: 15
    Last Post: 10-16-2006, 02:46 AM

Tags for this Thread

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