Oct 27 2021 11:16 AM
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.
Oct 27 2021 11:27 AM
@cox_sam I would create a pivot table from your data. It's perfectly suited for that. See attached.
Oct 27 2021 11:43 AM
Solution=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.
Nov 06 2021 08:04 PM
Nov 06 2021 08:08 PM
Nov 07 2021 09:36 AM
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.
Oct 27 2021 11:43 AM
Solution=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.