Search criteria and calculate sum of results.

TechRES
TechRES ✭✭
edited 05/30/23 in Formulas and Functions

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

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!