Formula works on original sheet but not when data is referenced on another sheet.

Zach_

Hi there.

I have a formula that works perfectly on the sheet that contains all of the original data. I have another sheet, which I use to reference several sheets, so that my dashboard only pulls from one sheet. When I transfer the working formula to this "dashboard source sheet," and update the references, the formula doesn't work.

The formula is intended to sum the design hours for all projects designated as "new," when the project was completed in the current fiscal year (between 7/1/2024 and 6/30/2025).

Are you able to identify where I'm going wrong? I've tried everything I can think of, but I can't get anything other than "unparseable."

Formula that is working:

=SUMIFS([Design Hours]:[Design Hours], [Date Completed]:[Date Completed], >=DATE(2024, 7, 1), [Date Completed]:[Date Completed], <=DATE(2025, 6, 30), [New or update?]:[New or update?], "New")

Formula that is not working:

=SUMIFS({Form Responses Design Hours}:{Form Responses Design Hours}, {Form Responses Date Completed}:{Form Responses Date Completed}, >=DATE(2024, 7, 1), {Form Responses Date Completed}:{Form Responses Date Completed}, <=DATE(2025, 6, 30), {Form Responses New or Update}:{Form Responses New or Update}, "New")

Thank you for the help.

Paul Newcome

A {Cross Sheet Reference} is the range.

You should use

{Range}

not

{Range}:{Range}

Zach_

I just tried deleting and re-adding all of the references. That didn't change anything.

Paul Newcome

A {Cross Sheet Reference} is the range.

You should use

{Range}

not

{Range}:{Range}

Zach_

That did it — thank you, Paul!

Paul Newcome

Happy to help. 👍️