I’ve been working on a summary sheet and by far have suceeded computing the monthly costs and data through this formula:
sumifs(A1:A,B2:B,">=1/1/2024",B2:B,"<2/1/2024")
sumifs(A1:A,B2:B,">=2/1/2024",B2:B,"<3/1/2024")
sumifs(A1:A,B2:B,">=3/1/2024",B2:B,"<4/1/2024")
sumifs(A1:A,B2:B,">=4/1/2024",B2:B,"<5/1/2024")
sumifs(A1:A,B2:B,">=5/1/2024",B2:B,"<6/1/2024")
sumifs(A1:A,B2:B,">=6/1/2024",B2:B,"<7/1/2024")
sumifs(A1:A,B2:B,">=7/1/2024",B2:B,"<8/1/2024")
sumifs(A1:A,B2:B,">=8/1/2024",B2:B,"<9/1/2024")
sumifs(A1:A,B2:B,">=9/1/2024",B2:B,"<10/1/2024")
sumifs(A1:A,B2:B,">=10/1/2024",B2:B,"<11/1/2024")
sumifs(A1:A,B2:B,">=11/1/2024",B2:B,"<12/1/2024")
sumifs(A1:A,B2:B,">=12/1/2024",B2:B,"<1/1/2024")
where A1:A is where the column of data I wanted to sum per month like cost, price, etc.
B2:B is the column where dates from January to December is listed. (January 1 to December 31 of this year, so you can imagine there was like 365 rows)
1/1/2024 & 2/1/2024 is the month January to February I wanted to compute.
But the problem is I want to compute my data per months starting from January to February, February to March and so on per cell. So there should be 12 copy of this formula in different months a year.
But, the dates won’t change if I drag down my formula. I have to manually set it to different months (2/1/2024 & 3/1/2024 or 3/1/2024 & 4/1/2024) which is time consuming bcs I have to do this for 50+ google sheets. Can anyone recommend anything on how to solve this. Thanks.
I expect to have 12 cells in a row consisting the formula I listed above, but I don’t have to change the date as I drag it down as it should automatically change per cell. How can we do this? Need your help, please.
2
There are several ways to do this. One option is-
=MAP(SEQUENCE(12),LAMBDA(x,SUMIFS(A2:A,B2:B,">="&DATE(2024,x,1),B2:B,"<="&EOMONTH(DATE(2024,x,1),0))))
Another option is to use QUERY() function.
=QUERY({A2:A,INDEX(EOMONTH(B2:B,0))},
"select Col2, sum(Col1)
where Col2>1
group by Col2
label sum(Col1) ''
format Col2 'MMM-YYYY'")
3