SUMIFS + range + criterion_range between 2 dates

Bmullan

Hello, community,

Struggling with a SUMIFS formula.

I've created a metric sheet to sum a specific column (E) in a project sheet by month. The column lists quantities for each mission (only at the parent row for each mission), spanning different months and years. While a simple SUM formula for the entire column gave the correct total, I'm struggling with a SUMIFS formula to calculate the monthly sum (Jan 24 shown below). Here's the formula I'm using:

=SUMIFS({Mission Schedule - SUMIFS Test Column E}, {Mission Schedule - SUMIFS Test Range 1}, ">=01/01/24", {Mission Schedule - SUMIFS Test End Date}, "<=1/31/24")

No errors, just returning 0 in the metric sheet.

Example mission data (test) and dates below (currently using this data for the metric sheet until I get a working formula):

Assuming my issue has to do with the date criteria.

Ric T

Hi @Bmullan,

Try this formula:

=SUMIFS({Mission Schedule - SUMIFS Test Column E}, {Mission Schedule - SUMIFS Test Range 1}, >=DATE(2024, 1, 1), {Mission Schedule - SUMIFS Test End Date}, <=DATE(2024, 12, 31))

Cheers,

Ric

Bmullan

@Ric T

Still getting a 0. Pink parentheses at DATE.

Ric T

@Bmullan

Hmm it works fine on my test sheet. May I confirm the following:

1. The Start Date and End Date column are both Date/Time column types.
2. Double-check that the range you selected for "SUMIFS Test Column E", "SUMIFS Test Range 1" and "SUMIFS Test End Date") correspond to the correct range of columns?

I would do a quick formula check of the whole column (instead of specifying a range). In the same sheet of your first screenshot, assuming that the column names are the same (e.g., one column titled "E", one column titled "Start Date", and one more column titled "End date"), try this:

=SUMIFS(E:E, [Start Date]:[Start Date], >=DATE(2024, 1, 1), [Finish Date]:[Finish Date], <=DATE(2024, 12, 31))