Forum Discussion

cox_sam's avatar
cox_sam
Copper Contributor
Oct 27, 2021
Solved

Autofill a Date Range within a Formula

I use many spreadsheets where I am counting the number of X per a date range. Is there anyway I can autofill the date range within my formulas that are setup to count?

 

Here is a pretty simple one:

=SUMIFS(H:H,A:A,">=8/1/2020",A:A,"<=8/31/2020")

Instead of dragging down for 12 months and then going back and manually changing the date range for every month, is there anyway I can set this up to autofill the rest of the year like 

=SUMIFS(H:H,A:A,">=9/1/2020",A:A,"<=9/30/2020"),

=SUMIFS(H:H,A:A,">=10/1/2020",A:A,"<=10/31/2020"), etc

 

I do have some much more complicated ones (=COUNTIFS(Data!I:I,O20,Data!E:E,"<=9/30/2021",Data!E:E,">=9/1/2021")+COUNTIFS(Data!I:I,O20,Data!E:E,"N/A",Data!C:C,"<=9/30/2021",Data!C:C,">=9/1/2021")+COUNTIFS(Data!I:I,O20,Data!E:E,"",Data!C:C,"<=9/30/2021",Data!C:C,">=9/1/2021")+COUNTIFS(Data!I:I,"Sam",Data!E:E,"<=9/30/2021",Data!E:E,">=9/1/2021")+COUNTIFS(Data!I:I,"Sam",Data!E:E,"N/A",Data!C:C,"<=9/30/2021",Data!C:C,">=9/1/2021")+COUNTIFS(Data!I:I,"Sam",Data!E:E,"",Data!C:C,"<=9/30/2021",Data!C:C,">=9/1/2021"))

which I understand is probably not possible but for the simple formula I think it should be.

excel
Formulas and Functions

5 Replies

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Oct 27, 2021

    cox_sam 

    =SUMIFS(C:C,A:A,">="&EDATE(E2,0),A:A,"<="&EDATE(E2,1)-1)

    You can enter this formula in G2 and copy down. 

     

    Maybe this can be done with the more complex formula as well.

    • cox_sam's avatar
      cox_sam
      Copper Contributor
      Nov 07, 2021
      I've never used the EDATE function before but I think with some modification, this could work for my current setup. Thank you!
    • cox_sam's avatar
      cox_sam
      Copper Contributor
      Nov 07, 2021
      That probably would work but currently I have over 1000 rows of data with over 100 different columns. Then I have a metrics spreadsheet which is checking for over 150 different things. If I were to completely restart, I could set it up for a pivot table but right now, that just wouldn't work. Thank you though!
      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Nov 07, 2021

        cox_sam 

        If with formulae I'd use in this case EOMONT() instead of EDATE() like

        =SUMIFS( C:C, A:A, ">=" &$E2, A:A, "<=" & EOMONTH($E2,0) )

         

        One more option for future is to use Home -> Analyze Data. It suggests some charts and PivotTables, but you may ask for something more different or complex

        which returns

        In my case it returns dynamic array formula which finds all months within source data and so summarization for them.

Resources