How to Sum by Month in Google Sheets
To sum by month in Google Sheets, you'll need to use a combination of functions like SUMIFS, DATE, and EOMONTH. Here's a step-by-step guide on how to do it:
- Organize your data: Make sure that you have a well-structured table with a date column and a data column that you want to sum. For example, let's say you have the date in column A and the amount in column B.
- Create a unique list of months: In a new column, use the UNIQUE function to create a list of unique months from your date data. For example, in cell D2, enter the following formula:
=UNIQUE(TEXT(A2:A, "MMMM yyyy"))This will create a unique list of months and years in column D.
- Sum by month: Now, in column E, you'll need to use the SUMIFS function to sum the data in column B based on the month and year in column D. In cell E2, enter the following formula:
=SUMIFS(B2:B, TEXT(A2:A, "MMMM yyyy"), D2)- Copy the formula: Drag the formula down from E2 to cover all the unique months in column D.
Now, you'll have a summary table in columns D and E, showing the total sum for each month.
Example
Here's an example to demonstrate how to sum by month in Google Sheets:
| A | B | D | E |
|---|---|---|---|
| 01/01/2021 | 100 | January 2021 | 300 |
| 01/02/2021 | 200 | February 2021 | 500 |
| 01/03/2021 | 50 | March 2021 | 150 |
| 01/03/2021 | 100 | ||
| 02/01/2021 | 300 | ||
| 02/02/2021 | 200 |
- Column A contains the dates
- Column B contains the amounts
- Column D contains the unique list of months, obtained using the formula
=UNIQUE(TEXT(A2:A, "MMMM yyyy")) - Column E contains the sum for each month, obtained using the formula
=SUMIFS(B2:B, TEXT(A2:A, "MMMM yyyy"), D2)
Did you find this useful?