Search criteria and calculate sum of results.

Options
✭✭
edited 05/30/23

I wish to update an inventory with how much stock is to be reordered.

My plan to do this is by finding the SUM of "NUMBER TAKEN" from form submissions below the stock list.

To do this I'm attempting to search criteria of the form submissions (lines 36-212) and calculate sum of results.

=SUMIF(TYPE\$36:TYPE\$212, HAS(TYPE2, ""), [NUMBER TAKEN]\$36:[NUMBER TAKEN]\$212)

My problem is that this results in 0 for all criteria.

Once I get the above working, I'll subtract the NUMBER TAKEN from STOCK COUNT.

=SUM([STOCK COUNT]2 - [NUMBER TAKEN]2)

Then, show whether reorder is needed.

=IF([STOCK COUNT]2 < [REORDER LEVEL]2, "REORDER NEEDED", "OK")

This has a weekly workflow for all lines showing "REORDER NEEDED" be sent via email.

What is the correct SUMIF formula, moreover, is there a better process to get to my desired outcome?

• ✭✭✭✭✭✭
Options

My suggestion would be to move the calculations to another sheet so that you can reference entire columns. This allows you to pick up new entries as they are added without having to adjust your formula. Then you can use something along the lines of...

=SUMIFS({Source Sheet Number Taken}, {Source Sheet Type}, @cell = Type@row)

• ✭✭✭✭✭✭
Options

My suggestion would be to move the calculations to another sheet so that you can reference entire columns. This allows you to pick up new entries as they are added without having to adjust your formula. Then you can use something along the lines of...

=SUMIFS({Source Sheet Number Taken}, {Source Sheet Type}, @cell = Type@row)

• ✭✭
Options

Thanks Paul,

Silly me, I didn't require HAS after all.

I've gone with your recommendation and created a separate sheet for form submissions.

It's working as expected.

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!