What is sum product...
Hi Stephen,
I dint get the following formulae.
It is often used for summing data subject to one or more conditions. In its
simplest form, this is equivalent to SUMIF. For example:
=SUMPRODUCT(--(A1:A5="dog"),B1:B5)
will sum data in column B where the corresponding entries in column A are
"dog". The bit with --( ) merely converts TRUE/FALSE to 1/0, which can be
used in calculating the products.
Can u eloborate this by giving an easy example.
"Stephen" wrote:
"Srikanth" wrote in message
...
Hi,
Can any one explain me what is sum product and how does it works...
And also the array....
Thanks in advance...
Let's take a simple example:
=SUMPRODUCT(A1:A5,B1:B5)
This takes the product of each corresponding pair of values and then sums
these. In other words, it calculates
A1xB1 + A2xB2 + A3xB3 + A4xB4 + A5xB5
It is often used for summing data subject to one or more conditions. In its
simplest form, this is equivalent to SUMIF. For example:
=SUMPRODUCT(--(A1:A5="dog"),B1:B5)
will sum data in column B where the corresponding entries in column A are
"dog". The bit with --( ) merely converts TRUE/FALSE to 1/0, which can be
used in calculating the products.
The great advantage over SUMIF is that multiple conditions are possible,
such as
=SUMPRODUCT(--(A1:A5="dog"),--(B1:B5="black"),C1:C5)
The arrays must all be the same size but do not need to be in adjacent
columns, for example:
=SUMPRODUCT(--(F4:F99910),--(D4:D999="abcde"),--(X4:X999<Sheet2!G7),--(J4:J999<=500),F4:F999,M4:M999)
will give the sum of product pairs in rows 4 to 999 of columns F and M where
all of the following conditions are satisfied:
column F is greater than 10;
column D contains text "abcde";
column X does not equal whatever is in cell G7 of Sheet2
column J is less than or equal to 500.
It's a very useful and versatile function. Post back if you have specific
questions.
|