Sum with two conditions

[Khally]

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.

 

[Max]

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

 

[Arjuna]

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)

 

[Khally]

Hi Arjuna,

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

 

[Max]

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

 

[Khally]

Hi Max,

Both the options are not working for me.

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

 

[Bob Phillips]

and you certainly don't need the leading +

--
HTH

Bob Phillips

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

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

 

[Khally]

did not find an answer yet
Khally

and you certainly don't need the leading +

--
HTH

Bob Phillips

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

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

 

[Max]

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]

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.

 

[Khally]

I did not understand which branch. please advise

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]

I did not understand which branch. please advise

Here's what I posted over there ..
-----

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

 

[Khally]

got it thanks,

Here's what I posted over there ..
-----


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]

Glad to hear that !
Thanks for calling home <g>