Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 --- |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 --- |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 --- |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 --- |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 --- |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 --- |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 --- |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Checking if 2 of 3 conditions are met... | Excel Discussion (Misc queries) | |||
Count using 2 conditions, one of which being a "less than or equal to" - URGENT | Excel Discussion (Misc queries) | |||
COUNT using multiple conditions | Excel Discussion (Misc queries) | |||
Counting occurrences of multiple conditions | Excel Discussion (Misc queries) | |||
How to multiple conditions to validate more than 2 conditions to . | Excel Worksheet Functions |