The SUMPRODUCT formula is an extremely powerful Excel formula. It enables you to take shortcuts in calculations by reducing the number of steps taken to calculate a result, thus saving you a few keystrokes.
Furthermore, it is more space-efficient than many other methods which may require you to create additional columns for the purpose of intermediate calculations.
Corality’s recently published a tutorial illustrating how to use the SUMPRODUCT formula to calculate revenue and to produce reports with revenue per product, per salesperson, etc.
The risks involved in using SUMPRODUCT
However, the conciseness and ‘space-efficiency’ of the SUMPRODUCT formula comes at a price – lack of transparency and readability. Thus, there is a trade-off between efficiency and transparency, because quicker “efficient” methods may result in a lack of transparency, thus increasing the likelihood of spreadsheet errors.
Rather than using the SUMPRODUCT formula, it may often be preferable to break down the formula and use additional columns for intermediate calculations. Even though this approach may appear inefficient, it does increase the transparency of the process.
Another important consideration is usability and how to ensure other users of the spreadsheet understands how SUMPRODUCT has been used. Alternative formulae such as SUMIF are usually more intuitive and easier to understand. The problem with SUMPRODUCT is that it may appear complicated, hence resulting in potential errors due to misuse and SUMPRODUCT formula may be hard to audit, thus making it more difficult to review.
Even though certain groups of people such as programmers, scientists and mathematicians may be accustomed to working with such formulae, we cannot assume that most users would be able to understand, use and audit the SUMPRODUCT. Thus, even though the SUMPRODUCT formula may appear attractive due to its conciseness, it should be avoided (where possible) to decrease the likelihood of introducing spreadsheet errors.
Advantages and disadvantages of SUMPRODUCT
Advantages of SUMPRODUCT:
- Relatively concise
- Space-efficient and fast to use
- Can incorporate multiple conditions
Disadvantages of SUMPRODUCT:
- Lack of transparency
- Hard to understand
- Hard to audit
Post new comment