Forum Discussion
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(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.
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!
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.