Hello, please help me

How to split sum by month? in googlesheets ?

CodePudding user response：

Try:

Formula in J3:

=INDEX(QUERY({TEXT(B3:B,"YYYY-MM"),H3:H},"Select Col1, sum(Col2) where Col2>0 group by Col1 label Col1 'month'"))

CodePudding user response：

Formula:

=ARRAYFORMULA(SUMIF(MONTH(A3:A); UNIQUE(MONTH(A3:A)); H3:H))

Explanation:

• MONTH(A3:A) extracts the month from the dates in column A
• UNIQUE(MONTH(A3:A)) finds the unique months from this list
• SUMIF() takes the month of each row as the range for the summation, the list of unique months as the criteria, and then the total values H3:H as the range to sum.
• ARRAYFORMULA() expands it to all rows below

CodePudding user response：

Use this formula

    = ArrayFormula(
  { TEXT(DATE(YEAR(TODAY());UNIQUE(MONTH(A3:A));DAY(TODAY())); "mmmm yyyy")\ IF(SUMIF(MONTH(A3:A); UNIQUE(MONTH(A3:A));H3:H)=0;;TEXT(SUMIF(MONTH(A3:A); UNIQUE(MONTH(A3:A)); H3:H); "Rp#,###"))})

CodePudding user response：

Use QUERY() function with date columns so that we can sort if needed. Use EOMONTH() to make group by month.

=QUERY({INDEX(IF(B2:B="",,EOMONTH(B2:B,0))),E2:E},"select Col1,sum(Col2) 
where Col1 is not null 
group by Col1
label Col1 'Month', sum(Col2) 'Total' 
format Col1 'MMM-YYYY'")