Re: What is sum product...

Suppose this is the data in A1:B5:

dog 1
cat 2
dog 3
dog 4
bird 5

The formula
=SUMPRODUCT(--(A1:A5="dog"),B1:B5)
will give the result 8, the total of column B where corresponding entries in
column A are "dog".

It works like this:
= 1*1 + 0*2 + 1*3 + 1*4 +0*5

The first number in each pair is 1 (if column A = "dog") or 0 otherwise. The
second number is the data from column B.


"Srikanth" <Srikanth@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:62A0700F-22E6-4C4B-BAB8-A3CE7475B4CB@xxxxxxxxxxxxxxxx
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" <Srikanth@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:0E4F6CF9-F2C0-4222-B1BD-B2E833734B19@xxxxxxxxxxxxxxxx
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:F999>10),--(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.





.

Loading