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

1. 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. 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. 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. 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. 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

User Tag List

Posting Permissions

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