Google Sheets intermediate sum date conditional formula

How to Sum Values Based on Date Criteria in Google Sheets?

Conditional Sum by Date is a Google Sheets function that sums values in column a where the corresponding dates in column b fall within the current month.. Formula Genius generates and validates this formula automatically from a plain-English prompt.

Summing values based on specific date ranges can be challenging, but this formula simplifies the process for current month, last 30 days, or specific quarters.

Generate This Formula Browse Library

The Formula

Prompt

"Sum values in Google Sheets where a date column falls within the current month, last 30 days, or a specific quarter"

Google Sheets 
=SUMIFS(A:A, B:B, ">="&EOMONTH(TODAY(), -1) + 1, B:B, "<="&EOMONTH(TODAY(), 0))

This formula sums values in column A where the corresponding dates in column B fall within the current month.

Step-by-Step Breakdown

  1. Use SUMIFS to sum values based on multiple criteria.
  2. Specify the range to sum (e.g., A:A for values).
  3. Define the criteria range (e.g., B:B for dates).
  4. Set the start date using EOMONTH to determine the first day of the current month.
  5. Set the end date using EOMONTH to determine the last day of the current month.

Edge Cases & Warnings

  • No values meet the date criteria, resulting in a sum of zero.
  • Dates in the specified range are formatted inconsistently.
  • The formula does not account for leap years when summing for February.
  • Using the formula in a different locale may cause date format issues.
  • The formula may return unexpected results if the date range overlaps with other criteria.

Examples

Prompt

"Values in A1:A10 and dates in B1:B10 for the current month"

Google Sheets 
Total sum of values where dates are in the current month
Prompt

"Values in A1:A10 and dates in B1:B10 for the last 30 days"

Google Sheets 
Total sum of values where dates are in the last 30 days

Frequently Asked Questions

Can I modify the formula for a specific quarter?

Yes, you can adjust the date criteria to reflect the start and end dates of the desired quarter.

What if my date column has blank cells?

Blank cells will be ignored in the summation, but ensure your date format is consistent.

How do I sum values for the last 30 days?

You can modify the criteria to use TODAY()-30 to set the start date for the last 30 days.

Can't find what you need?

Describe any formula in plain English and Formula Genius will generate, explain, and validate it — instantly.

Generate Custom Formula See Examples