sumifs pulling from separate page for last 45 days with additional criteria

82holt

I'm trying to pull a total quantity of bad parts that have been made in the last 45 days. I'm looking to break this down by specific part numbers. The formula that I'm currently using is :

=SUMIFS({Machine Shop Scrap Data Range 1}, {Machine Shop Scrap Data Range 2}, AND(@cell >= TODAY(-45), @cell <= TODAY()), {Machine Shop Scrap Data Range 4}, "10084273")

This seems to be a good formula, but it is providing a result of "0" for everything when I know there are quantities available. I'm worried that this may have something to do with my date column not being in a certain format as one of my other formulas wouldn't work until I changed the date column from a date selection box to a manual entry box.

Any ideas what may be going on? Thank you in advance.

NicoLHC

@82holt
That’s a great question you’re definitely on the right track with your formula! The issue is coming from two things:

1. AND() doesn’t work inside SUMIFS() the way you’re trying to use it. Instead, Smartsheet needs each date condition written separately.
2. Since you mentioned changing your date column to manual entry, there’s a chance some of those “dates” are actually being stored as text, which would cause the formula to return 0.

Here’s an adjusted version of your formula that should work:

=SUMIFS(
{Machine Shop Scrap Data Range 1},
{Machine Shop Scrap Data Range 2}, >= TODAY(-45),
{Machine Shop Scrap Data Range 2}, <= TODAY(),
{Machine Shop Scrap Data Range 4}, "10084273"
)

As a next step, I’d also double-check that your date column is still set to Column Type: Date (right-click the header → Edit Column Properties). If Smartsheet is reading the values as text instead of real dates, the formula won’t pick them up. A quick trick is to add a helper column with:
=IF(ISDATE([Your Date Column]@row ), "Date", "Text")

That will confirm if the sheet recognizes them as actual dates.

You’re very close — just a couple tweaks and you should see the right totals.