Editing a formula by tick box

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

Find more posts tagged with

Comments

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.

Sign in to join the conversation!

Quick Links

Trending discussions

Showing "stacked" data / Statuses of items by swimlane
I have multiple items that each have their own statuses and that roll up to "workstreams" or swimlanes. On the dashboard, I would like to show the progress of the items by swimlane without breaking it out into separate widgets for each. This would ideally be a stacked bar where each bar represented the Items and was broken…
How to Share Single Workspace Folder?
I have a workspace that I created with two subfolders. A user needs to have access to only one of them but when I have that folder selected, I can only share the entire workspace with them. Is there a secret way that I can share the parent folder and ensure that any new files created will be shared with the same people? I…
I have a Pro plan but I cannot create ANY reports
I have a Pro plan but I cannot create ANY reports. The system does not allow me to choose a folder, and consequently a sheet to create a row report and suggests an upgrade to Business or Enterprise. I'm quite new to Smartsheet and feel a bit disappointed with this limitation, or maybe I'm just doing something wrong. Alberto