Menu
I'm trying to make an in-company guide to Excel (we're a small non-profit and sorely need some sort of baseline guide). However, I've gotten stuck trying to explain the differences between SUMPRODUCT and SUMIFS.
My understanding is that SUMPRODUCT was used before Excel 2007 as a way to have multiple SUMIF criteria (among other things). Now that SUMIFS is available, is there any difference in the capacity of both formulas?
I've looked around a bit and found that SUMIFS tends to be faster, but that was it... Any insights and/or reading material is appreciated!
498
The Excel SUMPRODUCT function multiplies ranges or arrays together and returns the sum of products. This sounds boring, but SUMPRODUCT is an incredibly versatile function that can be used to count and sum like COUNTIFS or SUMIFS, but with more flexibility.
With SUMPRODUCT In spirit, the SUMPRODUCT option is closest to the SUMIFS formula since we are summing values based on multiple criteria. As before, it works fine as long as there is only one matching result.
The SUMPRODUCT function returns the sum of the products of corresponding ranges or arrays. The default operation is multiplication, but addition, subtraction, and division are also possible.
The SUMPRODUCT() function cross multiplies the two arrays, and then sums them. An alternative formula can be constructed using SUM() and IF() . Note that SUM can be replaced with SUMPRODUCT and the formula will work just the same.
SUMPRODUCT can actually sum the products from multiple arrays, SUMIFS will sum only values from a single range.
e.g. for input data
10 1
20 2
30 3
=SUMPRODUCT(A1:A3, B1:B3) => 10*1 + 20*2 + 30*3 = 140
200
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
