 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.

5 Replies

# Re: Autofill a Date Range within a Formula

@cox_sam I would create a pivot table from your data. It's perfectly suited for that. See attached.

best response confirmed by cox_sam (New Contributor)
Solution

# Re: Autofill a Date Range within a Formula

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

# Re: Autofill a Date Range within a Formula

I've never used the EDATE function before but I think with some modification, this could work for my current setup. Thank you!

# Re: Autofill a Date Range within a Formula

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!

# Re: Autofill a Date Range within a Formula

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.