Sum with two conditions
 

I want to calculate the sum of column with two conditions on different
worksheets,
Example
Data
Stock # QTY Month
01335 208 Aug-06
01337 402 Aug-06
01337 392 Aug-06
01335 400 Sep-06
01337 100 Sep-06
01337 102 Sep-06


If the stock # is equal to the stock # and Month equal to month
I need to get the result in another sheet as below.
Stock # Aug-06 Sep-06
01335 208 400
01337 794 202

Please help me.

Sum with two conditions
 

Source table assumed in sheet; Data, cols A to C, data from row2 down

In the new sheet, you have set up in cols A to C:

Stock # Aug-06 Sep-06
01335 ? ?
01337 ? ?

Put in B2:
=SUMPRODUCT((Data!$A$2:$A$10=$A2)*(Data!$C$2:$C$10 =B$1),Data!$B$2:$B$10)
Copy across and fill down to populate

Adapt the ranges to suit the extent of your source (use the smallest range
size). Note that SUMPRODUCT doesn't accept entire col references
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Khally" wrote:
I want to calculate the sum of column with two conditions on different
worksheets,
Example
Data
Stock # QTY Month
01335 208 Aug-06
01337 402 Aug-06
01337 392 Aug-06
01335 400 Sep-06
01337 100 Sep-06
01337 102 Sep-06


If the stock # is equal to the stock # and Month equal to month
I need to get the result in another sheet as below.
Stock # Aug-06 Sep-06
01335 208 400
01337 794 202

Please help me.

Sum with two conditions
 

Hi,

better to use sumif() as instructed below:

{=+SUM(IF($B$4:$B$9=$F15,IF($D$4:$D$9=H$14,$C$4:$C $9)))}

Note : is an array function so use ctrl+shift+enter & then Copy across and
fill down to populate

cheers....
Arjuna
Orange Business Solutions(India)



"Max" wrote:

Source table assumed in sheet; Data, cols A to C, data from row2 down

In the new sheet, you have set up in cols A to C:

Stock # Aug-06 Sep-06
01335 ? ?
01337 ? ?

Put in B2:
=SUMPRODUCT((Data!$A$2:$A$10=$A2)*(Data!$C$2:$C$10 =B$1),Data!$B$2:$B$10)
Copy across and fill down to populate

Adapt the ranges to suit the extent of your source (use the smallest range
size). Note that SUMPRODUCT doesn't accept entire col references
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Khally" wrote:
I want to calculate the sum of column with two conditions on different
worksheets,
Example
Data
Stock # QTY Month
01335 208 Aug-06
01337 402 Aug-06
01337 392 Aug-06
01335 400 Sep-06
01337 100 Sep-06
01337 102 Sep-06


If the stock # is equal to the stock # and Month equal to month
I need to get the result in another sheet as below.
Stock # Aug-06 Sep-06
01335 208 400
01337 794 202

Please help me.

Sum with two conditions
 

Hi Arjuna,

Can you elaborate on this function, the data is on one sheet and results on
the second sheet,



"Arjuna" wrote:

Hi,

better to use sumif() as instructed below:

{=+SUM(IF($B$4:$B$9=$F15,IF($D$4:$D$9=H$14,$C$4:$C $9)))}

Note : is an array function so use ctrl+shift+enter & then Copy across and
fill down to populate

cheers....
Arjuna
Orange Business Solutions(India)



"Max" wrote:

Source table assumed in sheet; Data, cols A to C, data from row2 down

In the new sheet, you have set up in cols A to C:

Stock # Aug-06 Sep-06
01335 ? ?
01337 ? ?

Put in B2:
=SUMPRODUCT((Data!$A$2:$A$10=$A2)*(Data!$C$2:$C$10 =B$1),Data!$B$2:$B$10)
Copy across and fill down to populate

Adapt the ranges to suit the extent of your source (use the smallest range
size). Note that SUMPRODUCT doesn't accept entire col references
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Khally" wrote:
I want to calculate the sum of column with two conditions on different
worksheets,
Example
Data
Stock # QTY Month
01335 208 Aug-06
01337 402 Aug-06
01337 392 Aug-06
01335 400 Sep-06
01337 100 Sep-06
01337 102 Sep-06


If the stock # is equal to the stock # and Month equal to month
I need to get the result in another sheet as below.
Stock # Aug-06 Sep-06
01335 208 400
01337 794 202

Please help me.

Sum with two conditions
 

"Arjuna" wrote:
... better to use sum(if(...)) [slightly corrected] ...

Why better <g? Perhaps as another option here, and its always good to know
of alternative ways to get things up .. imo, SP doesn't require
array-entering (except with TRANSPOSE nested within), and it is equally, if
not more directly intuitive to understand than the array-entered
Sum(if(...)). And ... it's easier to "forget" to array-enter /
re-array-enter an array formula than it is to use one which is entered
"normally"
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Sum with two conditions
 

Hi Max,

Both the options are not working for me.


"Max" wrote:

"Arjuna" wrote:
... better to use sum(if(...)) [slightly corrected] ...

Why better <g? Perhaps as another option here, and its always good to know
of alternative ways to get things up .. imo, SP doesn't require
array-entering (except with TRANSPOSE nested within), and it is equally, if
not more directly intuitive to understand than the array-entered
Sum(if(...)). And ... it's easier to "forget" to array-enter /
re-array-enter an array formula than it is to use one which is entered
"normally"
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Sum with two conditions
 

and you certainly don't need the leading +

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Max" wrote in message
...
"Arjuna" wrote:
... better to use sum(if(...)) [slightly corrected] ...

Why better <g? Perhaps as another option here, and its always good to
know
of alternative ways to get things up .. imo, SP doesn't require
array-entering (except with TRANSPOSE nested within), and it is equally,
if
not more directly intuitive to understand than the array-entered
Sum(if(...)). And ... it's easier to "forget" to array-enter /
re-array-enter an array formula than it is to use one which is entered
"normally"
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Sum with two conditions
 

did not find an answer yet
Khally

"Bob Phillips" wrote:

and you certainly don't need the leading +

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Max" wrote in message
...
"Arjuna" wrote:
... better to use sum(if(...)) [slightly corrected] ...

Why better <g? Perhaps as another option here, and its always good to
know
of alternative ways to get things up .. imo, SP doesn't require
array-entering (except with TRANSPOSE nested within), and it is equally,
if
not more directly intuitive to understand than the array-entered
Sum(if(...)). And ... it's easier to "forget" to array-enter /
re-array-enter an array formula than it is to use one which is entered
"normally"
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Sum with two conditions
 

"Khally" wrote:
Both the options are not working for me.

Then it could be a data consistency issue between what you actually have in
the source sheet: Data (in the "Stock#" col and/or in the "Month" col), and
what you have set up as the col / row headers in the other "summary" sheet.
If the data isn't consistent, then the SP formula as earlier suggested won't
work properly.

Anyway, here's a quick working sample for the earlier SP suggestion:
http://cjoint.com/?iomo3rW0Zs
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Sum with two conditions
 

See my response to you in the other branch. I've posted a link to a working
sample to illustrate. It's probably a data consistency issue you're facing
there.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Khally" wrote:
did not find an answer yet
Khally

Sum with two conditions
 

I did not understand which branch. please advise

"Max" wrote:

See my response to you in the other branch. I've posted a link to a working
sample to illustrate. It's probably a data consistency issue you're facing
there.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Khally" wrote:
did not find an answer yet
Khally

Sum with two conditions
 

"Khally" wrote:
I did not understand which branch. please advise

Here's what I posted over there ..
-----
"Khally" wrote:
Both the options are not working for me.

Then it could be a data consistency issue between what you actually have in
the source sheet: Data (in the "Stock#" col and/or in the "Month" col), and
what you have set up as the col / row headers in the other "summary" sheet.
If the data isn't consistent, then the SP formula as earlier suggested won't
work properly.

Anyway, here's a quick working sample for the earlier SP suggestion:
http://cjoint.com/?iomo3rW0Zs
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Sum with two conditions
 

got it thanks,

"Max" wrote:

"Khally" wrote:
I did not understand which branch. please advise

Here's what I posted over there ..
-----
"Khally" wrote:
Both the options are not working for me.

Then it could be a data consistency issue between what you actually have in
the source sheet: Data (in the "Stock#" col and/or in the "Month" col), and
what you have set up as the col / row headers in the other "summary" sheet.
If the data isn't consistent, then the SP formula as earlier suggested won't
work properly.

Anyway, here's a quick working sample for the earlier SP suggestion:
http://cjoint.com/?iomo3rW0Zs
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Sum with two conditions
 

Glad to hear that !
Thanks for calling home <g
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Khally" wrote:
got it thanks