What is the best way to get sum of entries/values for each week for each month?-CodePudding

I have a sheet in Google Sheets with game price entries for various dates and I want to find the weekly total for each month in the dataset.

I have tried the following formula but I didn't get any outuput, nor a formula error.

= ArrayFormula(IFERROR(SUMIFS(month($D$2:$D$22);2;WEEKNUM($D$2:$D$22);$B$2:$B$22)))**

Thank you.

CodePudding user response：

Suggestion:

I worked on it a little based on the table provided on your screenshot. I used a helper column to display week numbers using =ARRAYFORMULA(UNIQUE(WEEKNUM(D2:D22)))

And then used this formula and expanded it to match the rows based on Week Number =sumproduct(--(WEEKNUM(D2:D22 0,1)=F2),B2:B22) since WEEKNUM function is not designed to work with array values

We are still waiting for a demo sheet if this does not work for you if in case there's any modification needed.

CodePudding user response：

We can show it using is all in one-cell-formula:

=query({A2:G\arrayformula(if(month(F2:F)=1;weeknum(F2:F)-weeknum(eomonth(F2:F;-1)) weeknum(date(year(today());12;31));weeknum(F2:F)-weeknum(eomonth(F2:F;-1)) 1))\arrayformula(Text(F2:F;"MM"))\arrayformula(YEAR(F2:F))}; 
"select Col10, Col9, Col8, sum(Col2), avg(Col2), count(Col2) 
where Col1 is not null 
group by Col10, Col9, Col8
order by Col10 desc, Col9 desc, Col8 desc
label Col10 'Year', Col9 'Month', Col8 'Week of the Month', sum(Col2) 'Sum', avg(Col2) 'Average', count(Col2) 'Bought' 
format  sum(Col2) '#,##0.00 €', avg(Col2) '#,##0.00 €' ";0)

It takes columns A:G (1-7) and then adds a few more virtual columns that are later used by query:

Col8: Calculates week number in each month.

arrayformula(if(month(F2:F)=1;weeknum(F2:F)-weeknum(eomonth(F2:F;-1)) weeknum(date(year(today());12;31));weeknum(F2:F)-weeknum(eomonth(F2:F;-1)) 1))

Col9: Extracts month from the date in F column and converts it into text that can be used by query,

arrayformula(Text(F2:F;"MM"))

It was very difficult for me get correct months in this query, so instead of using month(Col6) I converted orginal data and added new column.

The only problem is that id does not diplay weeks when sales were 0.