Search criteria and calculate sum of results.
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 36212) 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?
Best Answer

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)
Answers

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)

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.

Happy to help. 👍️
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 61.2K Get Help
 320 Global Discussions
 197 Industry Talk
 415 Announcements
 4.2K Ideas & Feature Requests
 126 Brandfolder
 153 Just for fun
 124 Community Job Board
 441 Show & Tell
 26 Member Spotlight
 1 SmartStories
 276 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!