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

MegBurns23

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!!!

Paul Newcome

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(.....)

Paul Newcome

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(.....)

MegBurns23

Awesome, thank you so much Paul!!!

Paul Newcome

Happy to help. 👍️

MegBurns23

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

Paul Newcome

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

MegBurns23

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?

Paul Newcome

It is a cross sheet reference.

MegBurns23

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

MegBurns23

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

Paul Newcome

Happy to help. 👍️