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