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.
Assume that we are in the furniture renting out business. The inventory movement layout we will use is given on the right.
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:
Notice the “or”. Of course, beginning inventory would have the day the business started as the end date.
The picture below shows how SUMPRODUCT can be used to calculate the available inventory. Note the following:
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.
Please log in again. The login page will open in a new tab. After logging in you can close it and return to this page.