Can I keep a COUNTIF Total from checkmarks after checkmarks have been cleared?

Sam Lugiano

I am trying to track weekly inventory item usage so that I can deduct it from the master inventory amounts. This is the only part of my inventory system that I have not been able to automate via formulas. As an inventory item is used, a checkbox is checked and totaled. At the end of each week I manually deduct those totals from the master inventory amounts and then clear the Inventory Checklist to be used the next week.

I would like to be able to keep the "Total Week" amount so that I can use it in a formula to reduce the inventory automatically. Unfortunately I haven't figured out a way to do that because once I clear the checkmarks, the total returns to 0.

As a bonus, it would be great to have the checkboxes cleared automatically each week.

Thanks for your help in advance

Sam Lugiano

Great! Thanks so much. That was my last hurdle.

David Joyeuse

Hi!

Correct me if I'm reading you wrong.

But each time you're using an item in the week; then you check a box? And watching your sheet you may only be able to use it 12 times max.

I'm gonna suggest you another way of working this here.

First create another sheet called "Item used within week".

Have one column on this sheet: (we won't care of the main column in this case).

Column is "Item". Make sure this one is a drop down list with every items you may use within a week. This will have to match the list on your sheet above.

Have an automated rule on this sheet that moves all the row where "Item" is not blank to a binsheet (that has no other purpose than store all the lines you don't need anymore and you may delete anytime). Do this on every monday morning.

Then, create a form on this sheet.

Remove the main column question from the form, to only keep the "Item" column. Display this list in the way that suits you best.

Make sure in the parameters that the form refresh itself, each time a form is submitted.

On your sheet above, you can now get rid of all the checkboxes, and apply this formula to the Total Week column:

• =COUNTIFS({Item used within week - Item},Item@row)

The {Item used within week - Item} is a link toward the new sheet and the column "item". The formula will count for each Item how many times the name appear on the sheet "Item used within week".

Then, each time you want to record a new item simply load the form, check the item you want, submit the form.

The form will copy the line to the sheet, your inventory table will update itself each time you submit a form.

On every monday the form sheet will reset itself, and counters for each items will reset to 0.

Hope it helped!

Sam Lugiano

Great! Thanks so much. That was my last hurdle.