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 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?
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
- Customer Resources
- 65.9K Get Help
- 429 Global Discussions
- 146 Industry Talk
- 486 Announcements
- 5.2K Ideas & Feature Requests
- 86 Brandfolder
- 151 Just for fun
- 73 Community Job Board
- 498 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!