Google Sheets - Sum Elements of a Column's Range by Month

0
votes

Before making this post, I have read the following posts:

And the following websites' pages:

What I try to do is just to do a conditional sumation between the elements of a range contained in a column if they meet the condition of being within a month. The setup of my sheet is the following:

  • A named range DATES (A4:A1000)
  • A named range EXPENSES (C4:C1000)

And I have tried the following functions (A6 is a cell that contains a Date):

  • = SUMPRODUCT(EXPENSES, --(MONTH(DATES) = MONTH(A6))) [This one seems to work only in Excel]
  • = SUMIFS(EXPENSES, DATES, ">="&DATE(YEAR(A6), MONTH(A6), 1), DATES, "<="&EOMONTH(A6,0))
  • = ArrayFormula(SUMIFS(EXPENSES, MONTH(DATES), 12, YEAR(DATES), 2020))
  • ={{unique(ArrayFormula(text(DATES,"MMMM")))}, {ArrayFormula(sumif(ArrayFormula(text(DATES,"MMMM")), unique(ArrayFormula(text(DATES,"MMMM"))),EXPENSES))}}
  • etc.

All attempts return me a "Formula Parse Error" with the code #ERROR (Meaning Google Sheets cannot make sense of the formula you have written). I honestly don't know where is my error, whether I pass incorrect parameter types or forget to add braces, commas, etc.

2
dategoogle-sheetsgoogle-sheets-formula
SUMIFS() should work. Can you shoe some sample data? – Harun24HR

2 Answers

1
votes

The first formula you propose (the one using SUMPRODUCT) actually works for me. And actually the double negation is not even necessary. You may have problems if you have empty dates so you may just add a ISDATE(DATES) in the condition, like so:

=SUMPRODUCT(EXPENSES; ISDATE(DATES)*MONTH(DATES)=MONTH(A6))

Also note that a syntax error could be caused by your locale. Note that I'm using a semicolon ; instead of a comma , because in my locale commas are used as the decimal separator. Something similar might be happening to you.

References

0
votes

Please try one of the following formulas
(where A2:A is your dates range, A1 is your date and C2:C is your expenses range)

If all dates are within the same year try

=ArrayFormula(SUMIF(MONTH(A2:A),"="&MONTH(A1),C2:C))

If you have different years use

=ArrayFormula(SUMIF(MONTH(A2:A)&YEAR(A2:A),"="&MONTH(A1)&YEAR(A1),C2:C))