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

[BOB12345]

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

 

[oldbrewer]

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

 

[oldbrewer]

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

 

[BOB12345]

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.

 

[oldbrewer]

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

<colgroup><col><col span="9"><col><col span="2"></colgroup><tbody>
</tbody>