Editing a formula by tick box

Mitch@Enmach

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.

Andrée Starå

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

Mitch@Enmach

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å

Excellent!

Happy to help!

Andrée Starå

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.