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
 10.7K Get Help
 63 Global Discussions
 68 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 55 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!