SOLVED

Autofill a Date Range within a Formula

%3CLINGO-SUB%20id%3D%22lingo-sub-2891941%22%20slang%3D%22en-US%22%3EAutofill%20a%20Date%20Range%20within%20a%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2891941%22%20slang%3D%22en-US%22%3E%3CP%3EI%20use%20many%20spreadsheets%20where%20I%20am%20counting%20the%20number%20of%20X%20per%20a%20date%20range.%20Is%20there%20anyway%20I%20can%20autofill%20the%20date%20range%20within%20my%20formulas%20that%20are%20setup%20to%20count%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20a%20pretty%20simple%20one%3A%3C%2FP%3E%3CP%3E%3DSUMIFS(H%3AH%2CA%3AA%2C%22%26gt%3B%3D8%2F1%2F2020%22%2CA%3AA%2C%22%26lt%3B%3D8%2F31%2F2020%22)%3C%2FP%3E%3CP%3EInstead%20of%20dragging%20down%20for%2012%20months%20and%20then%20going%20back%20and%20manually%20changing%20the%20date%20range%20for%20every%20month%2C%20is%20there%20anyway%20I%20can%20set%20this%20up%20to%20autofill%20the%20rest%20of%20the%20year%20like%26nbsp%3B%3C%2FP%3E%3CP%3E%3DSUMIFS(H%3AH%2CA%3AA%2C%22%26gt%3B%3D9%2F1%2F2020%22%2CA%3AA%2C%22%26lt%3B%3D9%2F30%2F2020%22)%2C%3C%2FP%3E%3CP%3E%3DSUMIFS(H%3AH%2CA%3AA%2C%22%26gt%3B%3D10%2F1%2F2020%22%2CA%3AA%2C%22%26lt%3B%3D10%2F31%2F2020%22)%2C%20etc%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20do%20have%20some%20much%20more%20complicated%20ones%20(%3DCOUNTIFS(Data!I%3AI%2CO20%2CData!E%3AE%2C%22%26lt%3B%3D9%2F30%2F2021%22%2CData!E%3AE%2C%22%26gt%3B%3D9%2F1%2F2021%22)%2BCOUNTIFS(Data!I%3AI%2CO20%2CData!E%3AE%2C%22N%2FA%22%2CData!C%3AC%2C%22%26lt%3B%3D9%2F30%2F2021%22%2CData!C%3AC%2C%22%26gt%3B%3D9%2F1%2F2021%22)%2BCOUNTIFS(Data!I%3AI%2CO20%2CData!E%3AE%2C%22%22%2CData!C%3AC%2C%22%26lt%3B%3D9%2F30%2F2021%22%2CData!C%3AC%2C%22%26gt%3B%3D9%2F1%2F2021%22)%2BCOUNTIFS(Data!I%3AI%2C%22Sam%22%2CData!E%3AE%2C%22%26lt%3B%3D9%2F30%2F2021%22%2CData!E%3AE%2C%22%26gt%3B%3D9%2F1%2F2021%22)%2BCOUNTIFS(Data!I%3AI%2C%22Sam%22%2CData!E%3AE%2C%22N%2FA%22%2CData!C%3AC%2C%22%26lt%3B%3D9%2F30%2F2021%22%2CData!C%3AC%2C%22%26gt%3B%3D9%2F1%2F2021%22)%2BCOUNTIFS(Data!I%3AI%2C%22Sam%22%2CData!E%3AE%2C%22%22%2CData!C%3AC%2C%22%26lt%3B%3D9%2F30%2F2021%22%2CData!C%3AC%2C%22%26gt%3B%3D9%2F1%2F2021%22))%3C%2FP%3E%3CP%3Ewhich%20I%20understand%20is%20probably%20not%20possible%20but%20for%20the%20simple%20formula%20I%20think%20it%20should%20be.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2891941%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2891967%22%20slang%3D%22en-US%22%3ERe%3A%20Autofill%20a%20Date%20Range%20within%20a%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2891967%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1197864%22%20target%3D%22_blank%22%3E%40cox_sam%3C%2FA%3E%26nbsp%3BI%20would%20create%20a%20pivot%20table%20from%20your%20data.%20It's%20perfectly%20suited%20for%20that.%20See%20attached.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2892077%22%20slang%3D%22en-US%22%3ERe%3A%20Autofill%20a%20Date%20Range%20within%20a%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2892077%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1197864%22%20target%3D%22_blank%22%3E%40cox_sam%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DSUMIFS(C%3AC%2CA%3AA%2C%22%26gt%3B%3D%22%26amp%3BEDATE(E2%2C0)%2CA%3AA%2C%22%26lt%3B%3D%22%26amp%3BEDATE(E2%2C1)-1)%3C%2FP%3E%3CP%3EYou%20can%20enter%20this%20formula%20in%20G2%20and%20copy%20down.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMaybe%20this%20can%20be%20done%20with%20the%20more%20complex%20formula%20as%20well.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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

@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

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

I've never used the EDATE function before but I think with some modification, this could work for my current setup. Thank you!
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!

@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

image.png

which returns

image.png

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