Results 1 to 5 of 5

Thread: Adding days to a date range and embedding a formula using COUNTIFS
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Aug 2018
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Adding days to a date range and embedding a formula using COUNTIFS

    Hi

    I am trying use a COUNTIFS statement as I have a large data set on another worksheet.

    I am trying to count the number of cells and I have numerous criteria. The one which I can not get to work is:

    I have a Date Received column and I want to add 56 days to it in the formula combined with that number being less than a particular date (which is cell referenced).

    =COUNTIFS(ComplaintsData!$B$2:$B$1000000,F$1,ComplaintsData!$F$2:$F$1000000,"+56"&"<="&B3) is the formula that I was trying. The range for Date Received is ComplaintsData!$F$2:$F$1000000

    Can you please help.

    Thank you

  2. #2
    Board Regular
    Join Date
    Apr 2010
    Posts
    10,985
    Post Thanks / Like
    Mentioned
    13 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Adding days to a date range and embedding a formula using COUNTIFS

    against the "particular date " cell put a helper cell equal to particular date minus 56 - maybe

  3. #3
    Board Regular
    Join Date
    Apr 2010
    Posts
    10,985
    Post Thanks / Like
    Mentioned
    13 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Adding days to a date range and embedding a formula using COUNTIFS

    =COUNTIFS(ComplaintsData!$B$2:$B$1000000,F$1,ComplaintsData!$F$2:$F$1000000,<=B3)

  4. #4
    New Member
    Join Date
    Aug 2018
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Adding days to a date range and embedding a formula using COUNTIFS

    I need a dynamic formula as the size of the data will constantly vary and we are just copying and pasting this data in a raw worksheet. I did try using various other formulae such as ComplaintsData!$F$2:$F$1000000,">="&$B$3-56​ however when manually sorting the data and counting the numbers did match.

  5. #5
    Board Regular
    Join Date
    Apr 2010
    Posts
    10,985
    Post Thanks / Like
    Mentioned
    13 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Adding days to a date range and embedding a formula using COUNTIFS

    date rec'd key date 01/05/2018
    03/01/2018 key - 56 06/03/2018
    07/01/2018
    11/01/2018
    15/01/2018
    19/01/2018
    23/01/2018 how many received up to 6/3/18
    27/01/2018
    31/01/2018 16
    04/02/2018
    08/02/2018 =SUMPRODUCT(($A$2:$A$35<=$K$2)*1)
    12/02/2018
    16/02/2018
    20/02/2018
    24/02/2018
    28/02/2018
    04/03/2018
    08/03/2018
    12/03/2018
    16/03/2018
    20/03/2018
    24/03/2018
    28/03/2018
    01/04/2018
    05/04/2018
    09/04/2018
    13/04/2018
    17/04/2018
    21/04/2018
    25/04/2018
    29/04/2018
    03/05/2018
    07/05/2018
    11/05/2018
    15/05/2018

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •