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

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    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(.....)

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!