Search code examples
google-sheetsgoogle-sheets-formula

Sum range by month in Google Sheets with date conditional

I have a sheet with these columns:

  • A (Date): Range of dates for the year
  • B (Amount): An amount for an expense
  • D (Month): Name of each month in the year (e.g. June, July, etc)

I've tried this as suggested to work in other posts:

=SUMPRODUCT(B:B, ISDATE(A:A)*MONTH(A:A)=MONTH(D3&1))

I get the error Function MONTH parameter 1 expects number values. But 'Date' is a text and cannot be coerced to a number. with this.

If I remove the header, I just get a 0 for the sum for each month, which is incorrect.

How do I get this to work for each month the way my sheet is setup?

What the sheet currently looks like: https://docs.google.com/spreadsheets/d/1_8DQTa9aXGjvd7twL6RZMcFyqxa4Sg7eVTcD2wDXH2A/edit?usp=sharing

Solution

  • Try this instead. Not sure where you got that other formula:

    =SUMPRODUCT(B:B,TEXT(A:A,"mmmm")=D3)