SUMIFS Delivery Date >= Inventory Date

BettyJo

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?

Matt Lynn-PCG

@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.

BettyJo

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.

AravindGP

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}

BettyJo

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.