September 16

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 18th and 20th September? We just need to sum the numbers with the following two conditions:

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

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.2

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.


You may also like

All Excel LOOKUPs Explained

How to: Power Query File From Folder

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

Use this Bottom Section to Promote Your Offer

Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim