+ Reply to Thread
Results 1 to 9 of 9

How to count occurrences of a comparison within an array

  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.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,048

    Re: How to count occurrences of a comparison within an array

    See if this will work for you...
    =COUNTIF($B$2:$B$9,">'"&A2-5)
    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
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How to count occurrences of a comparison within an array

    Try this...

    Data Range
    A
    B
    C
    D
    1
    Order Received
    Order Processed
    ------
    Count
    2
    2/14/2014
    2/14/2014
    2
    3
    2/15/2014
    2/25/2014
    4
    n/a
    5
    2/16/2014
    canceled
    6
    2/16/2014
    2/17/2014
    7
    8
    n/a
    n/a
    9
    2/17/2014
    2/28/2014


    This array formula** entered in D2:

    =SUM(IF(IF(ISNUMBER(B2:B9),B2:B9,100000)-IF(ISNUMBER(A2:A9),A2:A9,1)<=5,1))

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

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

    Re: How to count occurrences of a comparison within an array

    I'm getting better results with that, but is seems to be comparing against just cell A2 instead of A3, A4, A5, etc. as appropriate.

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,048

    Re: How to count occurrences of a comparison within an array

    OK, I thought you wanted to copy that down to compare each row, sorry

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

    Re: How to count occurrences of a comparison within an array

    Okay, that seems to be giving me the correct results. Is there a way to add an element to screen for just a given month?

    For example, in a different cell I used the following formula to calculate for the whole year (entire spreadsheet):
    =SUMPRODUCT(--('2014'!$D$2:$D$1001<>""))

    ... and then variations on the following formula to get a breakdown by month:
    =SUMPRODUCT(--('2014'!$D$2:$D$1001<>""),--(MONTH('2014'!$D$2:$D$1001)=1))

  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: How to count occurrences of a comparison within an array

    If you're testing for a specific month, are all the dates within the same year?

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

    Re: How to count occurrences of a comparison within an array

    Yes, the intent is to start a new spreadsheet each year, so I only need a breakdown for each month for monthly reports in addition to the yearly summary.

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

    Re: How to count occurrences of a comparison within an array

    OK, this is getting kind of gnarly. Hope it doesn't get any more complicated!

    Data Range
    A
    B
    C
    D
    1
    Order Received
    Order Processed
    ------
    Count
    2
    2/14/2014
    2/14/2014
    2
    3
    2/15/2014
    2/25/2014
    4
    n/a
    5
    2/16/2014
    canceled
    6
    2/16/2014
    2/17/2014
    7
    1/12/2014
    1/15/2014
    8
    n/a
    n/a
    9
    2/17/2014
    2/28/2014


    This array formula entered in D2:

    =SUM(IF(IF(ISNUMBER(B2:B9),IF(MONTH(B2:B9)=2,B2:B9,100000),100000)-IF(ISNUMBER(A2:A9),IF(MONTH(A2:A9)=2,A2:A9,1),1)<=5,1))

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.

+ 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. [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