Sum only open orders with "Open" status.

Options

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 ✭✭✭✭✭
    Options
    =COUNTIFS(Status:Status,"OPEN",POs:POs,PO@Row
    

    ...

  • Heald33
    Heald33 ✭✭
    Options

    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 ✭✭✭✭✭✭
    Options

    Are you able to provide screenshots for context?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!