Sumifs- With a start and end date range Errors

Kris Berry

I am struggling with this formula. My Syntax is correct, the lay of this formula works until I add my date range. I have tried having both my formula column and the referenced date column as date, dates restricted, as well as just Text/number.

I have a years worth of data I want to calculate grand total of sheet, which is the first formula below. But I also would like the total of each month based on a few other criterias. So far the only solution I have found is to ensure my date is oldest to newest and limiting my Range3 and Range4 to the lines for the month I want the total for.

This formula works. but when I try to add in a >= date and a < date for my next 2 criteria I begin getting errors.

=SUMIFS({Order Log FY 2024 Range 2}, {Order Log FY 2024 Range 3}, =5435, {Order Log FY 2024 Range 4}, =7040220)

Here are the formulas I have tried so far.

Unparseable Error-

=SUMIFS({Order Log FY 2024 Range 2}, {Order Log FY 2024 Range 5}, ">="[Start Date]1, {Order Log FY 2024 Range 5},"<=" [Start Date]2)

=SUMIFS({Order Log FY 2024 Range 2}, {Order Log FY 2024 Range 5}, ">="& [Start Date]1, {Order Log FY 2024 Range 5},"<="& [Start Date]2)

=SUMIFS({Order Log FY 2024 Range 2}, {Order Log FY 2024 Range 3}, =5435, {Order Log FY 2024 Range 4}, =7040220, {Order Log FY 2024 Range 5},">="& DATE(2024,2,1), {Order Log FY 2024 Range 5},"<"& DATE(2024,3,1))

No space between & Date-

=SUMIFS({Order Log FY 2024 Range 2}, {Order Log FY 2024 Range 3}, =5435, {Order Log FY 2024 Range 4}, =7040220, {Order Log FY 2024 Range 5},">="&DATE(2024,2,1), {Order Log FY 2024 Range 5},"<"&DATE(2024,3,1))

=SUMIFS({Order Log FY 2024 Range 2}, {Order Log FY 2024 Range 3}, =5435, {Order Log FY 2024 Range 4}, =7040220, {Order Log FY 2024 Range 5}, ">="& "2/1/2024", {Order Log FY 2024 Range 5}, "<"& "3/1/2024")

Incorrect Argument Errors

=SUMIFS({Order Log FY 2024 Range 2}, {Order Log FY 2024 Range 3}, =5435, {Order Log FY 2024 Range 4}, =7040220, {Order Log FY 2024 Range 5}, ">=2/1/2024", {Order Log FY 2024 Range 5}, "< 3/1/2024")

Thank you for any help

Leibel S

@Kris Berry

Drop the quote marks.

for example:

=SUMIFS({Order Log FY 2024 Range 2}, {Order Log FY 2024 Range 5}, >= [Start Date]1, {Order Log FY 2024 Range 5},<= [Start Date]2)

Kris Berry

Hi Leibel S thank you for your response. When I changed the formula to:

=SUMIFS({Order Log FY 2024 Range 2}, {Order Log FY 2024 Range 5}, >=[Start Date]1, {Order Log FY 2024 Range 5}, <=[Start Date]2)

I received the Error: #INCORRECT ARGUMENT