+ Reply to Thread
Results 1 to 7 of 7

Count of rows in an array that meet criteria in column 2 and different criteria in column3

  1. #1
    Registered User
    Join Date
    11-01-2013
    Location
    California, USA
    MS-Off Ver
    Excel 2007
    Posts
    14

    Count of rows in an array that meet criteria in column 2 and different criteria in column3

    I have a table with 3 columns with an unkown number of rows (text, date, date) that is being imported daily.
    I want to create a 4th column with dates starting from today and each subsequent row be one day earlier. I want to look at 30 previous days.
    I then want to count the number of rows (looking at column 1-3) with the following criteria;
    Countif column2<= "date in colum4" AND column3< "date in column4"

    Thanks for any help.

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Count of rows in an array that meet criteria in column 2 and different criteria in col

    Try this

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    11-01-2013
    Location
    California, USA
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Count of rows in an array that meet criteria in column 2 and different criteria in col

    Jeff,
    That didn't work. I got the same value for each date. The value should be in the 90 - 120 range.
    12/5/2013 601
    12/4/2013 601
    12/3/2013 601
    12/2/2013 601
    12/1/2013 601

  4. #4
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Count of rows in an array that meet criteria in column 2 and different criteria in col

    I don't think I understand what your data looks like. The formula I provided looks at all the rows and counts up the number of rows in which the value in column B<=column D AND column C<Column D. You seem to want something in every row, instead of a single count of the times these criteria are met.

    Maybe my confusion is in this "last 30 days" part. Do you want a count, for example, in row 2 that counts the times this is true in rows 2-31? And then in row 3 it looks at rows 3-32, etc.?

    Maybe use this starting in row 2....

    =SUMPRODUCT(--(B2:B31<D2:D31),--(C2:C31<D2:D31))

    ...and copy down....

  5. #5
    Registered User
    Join Date
    11-01-2013
    Location
    California, USA
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Count of rows in an array that meet criteria in column 2 and different criteria in col

    6StringJazzer,
    I'm sorry I didn't explain this well enough.
    I need to know how many rows meet both criteria.
    For example; In the data below how many rows meet the criteria that Login Date <11/20/2013 AND Rcvd Date >11/21/2013. The count should be 4.
    It would even be better if it was in a pivot table so that I could drill down and see which rolls met those criteria.

    Roll Login Date Recvd Date
    19 10/7/2013 15:11 11/6/2013 6:46
    26 11/7/2013 15:12 11/16/2013 6:46
    30 11/7/2013 15:12 11/26/2013 6:46
    73 11/17/2013 15:13 11/26/2013 6:46
    79 11/17/2013 15:13 11/16/2013 6:46
    82 11/17/2013 15:13 11/26/2013 6:46
    19 11/27/2013 15:11 11/28/2013 6:46
    19 11/27/2013 15:11 11/28/2013 6:46
    19 11/17/2013 15:11 11/28/2013 6:46

  6. #6
    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
    52,917

    Re: Count of rows in an array that meet criteria in column 2 and different criteria in col

    Try this...
    =COUNTIFS($B$2:$B$10,"<11/20/2013",$D$2:$D$10,">11/21/2013")
    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

  7. #7
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: Count of rows in an array that meet criteria in column 2 and different criteria in col

    hi reynoldslarry. i couldn't see the link between your 1st post & post #5, so i'm basing it just on the latter. you could put the 2 dates somewhere say in F1 & G1:
    20Nov13
    21Nov13

    in Excel 2007 & above, you can use COUNTIFS ranging up the whole column:
    =COUNTIFS(B:B,"<"&F1,C:C,">"&G1)

    in lower versions, you can use SUMPRODUCT. but ranging up the whole column for this formula is a bad idea. so maybe just more than you would use like:
    =SUMPRODUCT((B2:B1000<F1)*(C2:C1000>G1))

    or you could convert your range into a Table. a Table would dynamically change its range when you have extra data. you could range up A1:C10 for eg & press CTRL + T to convert it. then your SUMPRODUCT formula would be like:
    =SUMPRODUCT((Table1[Login]<F1)*(Table1[Recvd]>G1))

    For doing a Pivot, i added a 4th column that determines if both conditions are met.
    =AND(B2<$F$1,C2>$G$1)

    copy down. you can see the attached file for the Pivot & the other formulas.
    Attached Files Attached Files

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

+ 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. specific count when rows meet certain criteria
    By D Thomas in forum Excel General
    Replies: 0
    Last Post: 01-13-2013, 08:35 PM
  2. [SOLVED] I want to use a formula to count how many rows meet a set of two criteria.
    By VivatMartin in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-20-2012, 10:46 AM
  3. Count # of rows that meet a criteria
    By miinstrel in forum Excel General
    Replies: 11
    Last Post: 07-26-2011, 04:38 PM
  4. Replies: 0
    Last Post: 10-14-2010, 05:04 PM
  5. Count rows which meet criteria
    By JonPugh in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-18-2007, 04:02 PM

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