The array arguments must have the same dimensions. If they do not, SUMPRODUCT returns the #VALUE! error value. For example, =SUMPRODUCT(C2:C10,D2:D5) will return an error since the ranges aren't the same size.
SUMPRODUCT treats non-numeric array entries as if they were zeros.
Basic function like below where range C2:D5 contained numbers.
SUMPRODUCT(C2:C5,D2:D5)
The longer formula is C2*D2+C3*D3+C4*D4+C5*D5 will return the same result.
There is a little bit advance function when there are specific conditions required. For example, in below case, there are repetitive characters in range B2:B10 and C2:C10. There are conditions to sum the products of B2:B10 and C2:C10 that are located in cell B15 and C15 respectively.
SUMPRODUCT((B2:B10=B15)*(C2:C10=C15)*D2:D10)
More advance function to find SUMPRODUCT of visible cell only per below. In this function, hidden rows will be considered as 0.
SUMPRODUCT(B2:B10,SUBTOTAL(9,OFFSET(C2:C10,ROW(C2:C10)-MIN(ROW(C2:C10)),0,1)))
No comments:
Post a Comment