Editing a formula by tick box

Options
Mitch@Enmach
Mitch@Enmach ✭✭
edited 12/09/19 in Smartsheet Basics

Hello all,

I am wanting to add a new function to my sheet with a formula but I am unsure how to go about it. I have attached screen shots to make what I am trying to do a bit clearer.

Right now I have a number of formulas checking the "Product Ordered" column for particular products on order and reporting how many of each there are.

 =SUMIF([PRODUCT ORDERED]:[PRODUCT ORDERED], CUSTOMER83, QTY:QTY)

I would like to modify this formula so that when a tick box in the "Dispatch Done" column is ticked it no longer counts the "Product Ordered" from that row. 

Further to this I would like to set up another column with a formula that does the opposite, reports the numbers products dispatched. 

Many thanks in advance.

Smartsheet Screen Shot.png

Comments

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    Hi Mitch,

    Try something like this.

    To get the dispatched number just change the 0 in the end to 1.

    =SUMIFS(QTY:QTY; [PRODUCT ORDERED]:[PRODUCT ORDERED]; CUSTOMER@row; [Dispatch Done]:[Dispatch Done]; 0)

    The same version but with the below changes for your and others convenience.

    =SUMIFS(QTY:QTY, [PRODUCT ORDERED]:[PRODUCT ORDERED], CUSTOMER@row, [Dispatch Done]:[Dispatch Done], 0)

    Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

    Did it work?

    I hope that helps!

    Have a fantastic week!

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

    work-bold

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Mitch@Enmach
    Options

    Thank you very much for your time Andrée the formula works perfectly. 

    One question I had though is why if I place the formula in an empty cell within the "Product Ordered column" does it work but then a second formula in the cell below will throw the first to"Circular reference" and the rest to "Blocked"

    Many thanks again

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    Excellent!

    Happy to help!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    More info:

    #CIRCULAR REFERENCE

    Cause

    The formula references itself. The circular reference may be direct where the reference is in the formula text itself, or indirect where this formula references a cell which then references back to this cell.

    Resolution

    Determine which reference is circular. Indirect references can be many levels deep. Sometimes it is easiest to make a copy of the formula and remove cell references until the error is eliminated. This process of elimination will help you see which reference is ultimately circular.

    #BLOCKED

    Cause

    The calculation is blocked because at least one of the cells referenced by the formula has an error.

    Resolution

    Determine which cell referenced by this formula contains an error, which will be more descriptive of the problem.

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.