# SUMIFS - Stumped on calculation to figure out if within a month

Options
✭✭✭

Hi All,

I am using SUMIFS to calculate if a part is in the Spend Formulas Range sheet and then if that same part has a Date that is within the month of January. The formula seems to break when the dates come in.

=SUMIFS({2023 Spend Formulas Range 1}, {2023 Spend Formulas Range 2}, [Original Material #]@row, VLOOKUP([Original Material #]@row, {2023 Spend Formulas Range}, 4, 8, false) <= "1 / 31 / 23")

And if I was to duplicate this formula to go out to three sheets: "spend", "open orders", anSod "forecast" can I just combine all the SUMIFS within a SUM?

So:

=SUM(SUMIFS({2023 Spend Formulas Range 1}, {2023 Spend Formulas Range 2}, [Original Material #]@row, VLOOKUP([Original Material #]@row, {2023 Spend Formulas Range}, 4, 8, false) <= "1 / 31 / 23"), =SUMIFS({2023 Open Orders Formulas Range 1}, {2023 Open Orders Formulas Range 2}, [Original Material #]@row, VLOOKUP([Original Material #]@row, {2023 Open Orders Formulas Range}, 4, 8, false) <= "1 / 31 / 23"), SUMIFS({2023 Forecast Formulas Range 1}, {2023 Forecast Formulas Range 2}, [Original Material #]@row, VLOOKUP([Original Material #]@row, {2023 Forecast Formulas Range}, 4, 8, false) <= "1 / 31 / 23")

Any help or advice to get these formulas to work is greatly appreciated!!!

• ✭✭✭✭✭✭
Options

You need something more like this:

=SUMIFS({2023 Spend Formulas Range 1}, {2023 Spend Formulas Range 2}, [Original Material #]@row, {Date Range}, AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = 2023))

Then yes. You can drop them all inside of a SUM function or you can add them all together.

=SUM(SUMIFS(.....), SUMIFS(.....), SUMIFS(.....))

or

=SUMIFS(.....) + SUMIFS(.....) + SUMIFS(.....)

• ✭✭✭✭✭✭
Options

You need something more like this:

=SUMIFS({2023 Spend Formulas Range 1}, {2023 Spend Formulas Range 2}, [Original Material #]@row, {Date Range}, AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = 2023))

Then yes. You can drop them all inside of a SUM function or you can add them all together.

=SUM(SUMIFS(.....), SUMIFS(.....), SUMIFS(.....))

or

=SUMIFS(.....) + SUMIFS(.....) + SUMIFS(.....)

• ✭✭✭
Options

Awesome, thank you so much Paul!!!

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

• ✭✭✭
Options

@Paul Newcome , I am getting a Circular Reference. The @cell is the date cell, correct? If not can you explain?

• ✭✭✭✭✭✭
Options

Once you create the cross sheet references, you should leave everything else as is. @cell should stay as @cell in the formula.

• ✭✭✭
Options

Ok thank you @Paul Newcome . The {Date Range} should this be a cross sheet reference to the date column on the other sheet or 01/01/23-01/31/23?

• ✭✭✭✭✭✭
Options

It is a cross sheet reference.

• ✭✭✭
Options

Thank you @Paul Newcome ! I plugged in the date but I am getting an #IncorrectArgument error now.

• ✭✭✭
Options

@Paul Newcome I figured it out, I was pointing the date range at the wrong column! THANK YOU SO MUCH!!!!

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!