Sum only open orders with "Open" status.

It is possible for POs to have several different part numbers on it, as well as the same part number be on multiple POs. It is also possible for POs to be partially complete, therefore, each line item has its own status.

I am wanting to get the total number of each part needed to put on the schedule. The issue I am running into is, if the first instance of a part number that is in our master order log is "Closed", it ignores any other instance of that part number that may be in the log.

What is the best way to work through this problem?

Answers

  • heyjay
    heyjay ✭✭✭✭✭
    =COUNTIFS(Status:Status,"OPEN",POs:POs,PO@Row
    

    ...

  • Heald33
    Heald33 ✭✭

    Let me expand on this a little more...

    I have a sheet that has all of the POs and individual parts for each PO on it. I have a separate sheet (Gantt chart) that I use for the schedule. It has a list of every part on it. It also has the quantity for each part. I am wanting the quantity fields to look at the part number that identifies the row, find it in the Order Log, and then add all matching parts that have an "Open" status.

    I'm currently using =SUMIFS({Orders Range 1}, {Orders Range 2}, [Part Number]@row, {Orders Range 3}, "<>Complete", {Orders Range 3}, "<>Shipping", {Orders Range 3}, "<>Closed") but it seems to stop counting if it encounters a matching part that has one of these "NOT" statuses.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Are you able to provide screenshots for context?

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!