September 16

Using SumProduct in Inventory Management Spreadsheets

0  comments

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.

1

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.


Tags


You may also like

51: Oz du Soleil & the Global Excel Summit 2021

51: Oz du Soleil & the Global Excel Summit 2021

Global Excel Summit 2021

Global Excel Summit 2021
{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

Free Power User Quick Guide

Discover The BEST Ways To Use Lookups And Conditional Calculations Quickly And Easily With This Reference Guide -- You Won’t Want This To Leave Your Side

With so many ways to use Excel, it can be difficult to memorize all of the key functions, calculations, and techniques you can employ to meet your goal: simply get the job done.

>