Using SumProduct in Inventory Management Spreadsheets

In many situations, particularly when it comes to inventory management, there is a need to do SUMPRODUCT with conditions. We can definitely implement it using many hidden calculations, but isn’t it elegance that we strive for?

Join Oz du Soleil to learn about an amazing trick that will change how you think about Excel formulas.

What are you waiting for? Let’s jump in.

1 – The Layout

Assume that we are in the furniture renting out business. The inventory movement layout we will use is given on the right.

2 – Available Inventory

Can you see how we can calculate the number of desks available between 18^th and 20^th September? We just need to sum the numbers with the following two conditions:

• The start date should be after 20^th September, or
• The end date should be before 18^th

Notice the “or”. Of course, beginning inventory would have the day the business started as the end date.

3 – The Calculation

The picture below shows how SUMPRODUCT can be used to calculate the available inventory. Note the following:

• When using conditions within the formula, each condition should be contained in a set of parentheses.
• “*” signifies the logical “and” operator and “+” signifies the logical “or” operator.
• Unlike other Boolean expressions on ranges, these conditions are evaluated to 1’s and 0’s instead of True and False respectively. That is why the product is defined and does not throw out an error.
• The ranges to be multiplied subject to the conditions require the syntax (range1)*(range2)*(range3). In this case, we have only one such range, the column (L) containing numbers. Hence, the SUMPRODUCT yields a sum.

4 – Usefulness

For starters, you do not need to have columns with hidden calculations to do SUMPRODUCT with conditions. What if you had many rows with different conditions and each row required a SUMPRODUCT based on those conditions? This elegant solution can save you from doing tens or even hundreds of hidden calculations in columns besides your data.

You can also use SUMPRODUCT as an array formula encapsulated within the SUM function. This way you would be summing up multiple SUMPRODUCTs with conditions into a single cell. The possibilities are as crazy as it gets!

What’s next?

Get your hands dirty and implement this awesome trick. Try out simple examples. And when you get comfortable with this, start employing it in your work.

And do not forget to share this technique with your friends or colleagues, and write to us with your experiences in the comments section below.

• Author
• Recent Posts

Jamani Arsalan

I am a healthcare consultant, currently based in Middle East, at an international professional services firm. My work largely revolves around project management, and statistical analysis. And my professional interests include developing my knowledge within the discipline of health analytics.

Latest posts by Jamani Arsalan (see all)

• SSSVEDA DAY 7 – Every Team Needs Someone Who Understands Data - February 18, 2018
• SSSVEDA DAY 5 – When Data Analysis is Wrong - October 31, 2017
• SSSVEDA DAY 4 – Sharing the Excel Knowledge - July 18, 2017