Hi,

Can any one explain me what is sum product and how does it works...
And also the array....

Thanks in advance...

Hi Srikanth,

Bob Phillips explains it very well here,
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

HTH
Martin


"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...

Hi,

For Sumproduct have a look he-

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

and for help on array formula

http://www.cpearson.com/excel/ArrayFormulas.aspx

Mike

"Srikanth" wrote:

Hi,

Can any one explain me what is sum product and how does it works...
And also the array....

Thanks in advance...

"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.

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.

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" wrote in message
...
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.

STEPHEN...

THAT WAS AN EXCELLENT REPLY...
THANKS DUDE...


"Stephen" wrote:

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" wrote in message
...
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.

Hi Martin,

Thanks for the reply..

"MartinW" wrote:

Hi Srikanth,

Bob Phillips explains it very well here,
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

HTH
Martin


"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...

Hi Mike,

Thank you so much for the reply..

"Mike H" wrote:

Hi,

For Sumproduct have a look he-

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

and for help on array formula

http://www.cpearson.com/excel/ArrayFormulas.aspx

Mike

"Srikanth" wrote:

Hi,

Can any one explain me what is sum product and how does it works...
And also the array....

Thanks in advance...