Adding row values based on date

bleinfuss · 09/21/22

Hello Smartsheet Community,

I am trying to create a reconciliation system in Smartsheet for the on-hand count vs the dispensed count of an item. The idea is to have the end user submit a morning count of 3 different products every morning. Through another form, submit a dispensed amount for the products throughout the day (will be multiple form submissions per day). A 3rd form will be filled out by the end user for restock counts.

In the Smartsheet, I am trying to take the morning count minus the dispensed amounts through the day and reconcile it to show a match or discrepancy against the next morning's count,

Any help on this would be greatly appreciated

Thank you!

SmartLew · 09/21/22

Hi,

Where are you trying to show the final figure? In a separate sheet? Will you have a static list of items to count against?

Is this along the right lines?

Form intake:

Three different forms capturing the date , item, type of submission and count

Summary Sheet:

Showing by item:

The sum of morning count for todays date

=SUMIFS({intake sheet count}, {intake sheet item}, ITEM@row, {intake sheet type}, "Morning Count", {intake sheet date submitted}, TODAY())

sum of morning count minus dispensed count for yesterdays date, (not sure if this should also add the count of restock? I haven't included that in the formula so let me know!)

=SUMIFS({intake sheet count}, {intake sheet item}, ITEM@row, {intake sheet type}, "Morning Count", {intake sheet date submitted}, TODAY(-1)) - SUMIFS({intake sheet count}, {intake sheet item}, ITEM@row, {intake sheet type}, "Dispensed", {intake sheet date submitted}, TODAY(-1))

and then the difference between those two columns.

=[This Morning Count]@row - [Yday Morning-Dispensed]@row

Is this what you are after?

SmartLew · 09/21/22

https://community.smartsheet.com/discussion/comment/344305#Comment_344305

Just realised that in fact you most likely want the reconciliation in the next mornings row in the intake sheet, doing away with the summary sheet:

Adding row values based on date

Answers

Help Article Resources

Categories