Formulas and Functions

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

SUMIFS Delivery Date >= Inventory Date

I'm attempting to write a SUMIFS formula where one of the criterion is to compare one date to another. It is returning 0 when it should return 1830.

=SUMIFS({PURCHASING QTY EA}, {SHIP TO PLANT}, "HUBER HEIGHTS", {CONDUIT PURCHASING JDE CODE}, "RPVC0412200", {CONDUIT DELIVERY DATE}, >={CONDUIT INVENTORY DATE}, {CONDUIT DELIVERY DATE}, <=DATE(2024, 8, 31))

What am I missing?

Tags:

Answers

  • Community Champion

    @BettyJo You should prob use a sum(collect()). Try this:

    =sum(collect({PURCHASING QTY EA}, {SHIP TO PLANT}, "HUBER HEIGHTS", {CONDUIT PURCHASING JDE CODE}, "RPVC0412200", {CONDUIT DELIVERY DATE}, >={CONDUIT INVENTORY DATE}, {CONDUIT DELIVERY DATE}, <=DATE(2024, 8, 31)))

    Also sometimes using dates as a range can cause an error if the source date/row is empty. So either add a criteria to only see rows that have a date, or you can also but the date ref in an iferror() to cancel out those blank rows.

    Matt Lynn

  • Tried your formula and still getting 0 instead of 1830.

    Here is my source sheet:

    Here is my formula sheet:

    The formula is for 4" GSK in Huber Heights in August 2024 should result in 1830, as per the top row of the source sheet.

  • Community Champion

    Hi @BettyJo

    Create a helper column in your source to tag rows which meet the criteria of Delivery Date being greater than or equal to Inventory date. It can be a simple checkbox column which has the formula =IF([Conduit Delivery Date]@row >= [Conduit Inventory Date]@row, 1, 0).

    You can then use the newly created helper column with its value (in the above formula example, it will be "True") in your formula to replace {CONDUIT DELIVERY DATE}, >= {CONDUIT INVENTORY DATE}

    Thanks,

    Aravind GP| Delivery Manager

    Atturra Data & Integration

    M: +61493337445

    E:Aravind.GP@atturra.com

    W: www.atturra.com

  • This formula is being a BEAR! I really appreciate your help on this.

    I did what you suggested, but result is still 0. Here's the revised formula:

    =SUM(COLLECT({PURCHASING QTY EA}, {SHIP TO PLANT}, "HUBER HEIGHTS", {CONDUIT PURCHASING JDE CODE}, "RPVC0412200", {CONDUIT METRICS HELPER}, "TRUE", {CONDUIT DELIVERY DATE}, <=DATE(2024, 8, 31)))

    Here's the source sheet with the Metrics Helper, which is working correctly.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions