Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
calulate amount based on multi condition
Hi,
I have a table similar as below, how can I calculate the amount of A during December 1 to 7? I know I can use DSUM for sigle result, but my purpose is to consoladate a table for A, B, C. Is there any formula I can use? coloum A coloum B coloum C 1/12/09 A 100 5/12/09 C 50 9/12/09 A 120 8/12/09 B 80 4/12/09 A 90 Thanks a lot! -- Lowan |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
calulate amount based on multi condition
Hi,
=SUMPRODUCT((A1:A10=DATE(2009,12,1))*(A1:A10<=DAT E(2009,12,7))*(B1:B10="A")*(C1:C10)) Mike "Lowan Chan" wrote: Hi, I have a table similar as below, how can I calculate the amount of A during December 1 to 7? I know I can use DSUM for sigle result, but my purpose is to consoladate a table for A, B, C. Is there any formula I can use? coloum A coloum B coloum C 1/12/09 A 100 5/12/09 C 50 9/12/09 A 120 8/12/09 B 80 4/12/09 A 90 Thanks a lot! -- Lowan |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
calulate amount based on multi condition
Hi, Mike,
I tried, but doesn't work..... I have another question, if i have all the year's data on this table, how can i caculate the amount based on week 1, week 2, week 3...? -- Lowan "Mike H" wrote: Hi, =SUMPRODUCT((A1:A10=DATE(2009,12,1))*(A1:A10<=DAT E(2009,12,7))*(B1:B10="A")*(C1:C10)) Mike "Lowan Chan" wrote: Hi, I have a table similar as below, how can I calculate the amount of A during December 1 to 7? I know I can use DSUM for sigle result, but my purpose is to consoladate a table for A, B, C. Is there any formula I can use? coloum A coloum B coloum C 1/12/09 A 100 5/12/09 C 50 9/12/09 A 120 8/12/09 B 80 4/12/09 A 90 Thanks a lot! -- Lowan |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
calulate amount based on multi condition
Hi,
I tried, but doesn't work..... That isn't a very helpful explanation of what went wrong but if it didn't work then you need to check your data as follows:- 1. Are the dates in column A really dates. Try adding 1 day to one of the dates and see what happens. 2. Are there any leading/trailing spaces in column B 3. Is column C really a number? Try adding 1 to one of the numbers to test. As for summing by week if my first answer didn't work then this is unlikely to but here goes. Create a helper column with this formula in =WEEKNUM(A1) and drag down To sum use this where D is the helper column and the week number you want is 49 =SUMPRODUCT((D1:D10=49)*(B1:B10="A")*(C1:C10)) Mike "Lowan Chan" wrote: Hi, Mike, I tried, but doesn't work..... I have another question, if i have all the year's data on this table, how can i caculate the amount based on week 1, week 2, week 3...? -- Lowan "Mike H" wrote: Hi, =SUMPRODUCT((A1:A10=DATE(2009,12,1))*(A1:A10<=DAT E(2009,12,7))*(B1:B10="A")*(C1:C10)) Mike "Lowan Chan" wrote: Hi, I have a table similar as below, how can I calculate the amount of A during December 1 to 7? I know I can use DSUM for sigle result, but my purpose is to consoladate a table for A, B, C. Is there any formula I can use? coloum A coloum B coloum C 1/12/09 A 100 5/12/09 C 50 9/12/09 A 120 8/12/09 B 80 4/12/09 A 90 Thanks a lot! -- Lowan |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
calulate amount based on multi condition
Hi Mike,
oh, sorry, it was my mistake. it works. how about to calculate base on each week, week 1, week 2...... thanks & regards, Lowan -- Lowan "Mike H" wrote: Hi, =SUMPRODUCT((A1:A10=DATE(2009,12,1))*(A1:A10<=DAT E(2009,12,7))*(B1:B10="A")*(C1:C10)) Mike "Lowan Chan" wrote: Hi, I have a table similar as below, how can I calculate the amount of A during December 1 to 7? I know I can use DSUM for sigle result, but my purpose is to consoladate a table for A, B, C. Is there any formula I can use? coloum A coloum B coloum C 1/12/09 A 100 5/12/09 C 50 9/12/09 A 120 8/12/09 B 80 4/12/09 A 90 Thanks a lot! -- Lowan |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
calulate amount based on multi condition
Hi,
Glad I could help, see my other post for summing by week. Mike "Lowan Chan" wrote: Hi Mike, oh, sorry, it was my mistake. it works. how about to calculate base on each week, week 1, week 2...... thanks & regards, Lowan -- Lowan "Mike H" wrote: Hi, =SUMPRODUCT((A1:A10=DATE(2009,12,1))*(A1:A10<=DAT E(2009,12,7))*(B1:B10="A")*(C1:C10)) Mike "Lowan Chan" wrote: Hi, I have a table similar as below, how can I calculate the amount of A during December 1 to 7? I know I can use DSUM for sigle result, but my purpose is to consoladate a table for A, B, C. Is there any formula I can use? coloum A coloum B coloum C 1/12/09 A 100 5/12/09 C 50 9/12/09 A 120 8/12/09 B 80 4/12/09 A 90 Thanks a lot! -- Lowan |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
calulate amount based on multi condition
Hi, Mike,
I have another problem. I found that I can use these format in excel 2007 perfectly, but not in 97-2003 version. Once I save to 97-2003, all the result of fomula end up "#value". How can I solve this? thanks a lot! -- Lowan "Mike H" wrote: Hi, I tried, but doesn't work..... That isn't a very helpful explanation of what went wrong but if it didn't work then you need to check your data as follows:- 1. Are the dates in column A really dates. Try adding 1 day to one of the dates and see what happens. 2. Are there any leading/trailing spaces in column B 3. Is column C really a number? Try adding 1 to one of the numbers to test. As for summing by week if my first answer didn't work then this is unlikely to but here goes. Create a helper column with this formula in =WEEKNUM(A1) and drag down To sum use this where D is the helper column and the week number you want is 49 =SUMPRODUCT((D1:D10=49)*(B1:B10="A")*(C1:C10)) Mike "Lowan Chan" wrote: Hi, Mike, I tried, but doesn't work..... I have another question, if i have all the year's data on this table, how can i caculate the amount based on week 1, week 2, week 3...? -- Lowan "Mike H" wrote: Hi, =SUMPRODUCT((A1:A10=DATE(2009,12,1))*(A1:A10<=DAT E(2009,12,7))*(B1:B10="A")*(C1:C10)) Mike "Lowan Chan" wrote: Hi, I have a table similar as below, how can I calculate the amount of A during December 1 to 7? I know I can use DSUM for sigle result, but my purpose is to consoladate a table for A, B, C. Is there any formula I can use? coloum A coloum B coloum C 1/12/09 A 100 5/12/09 C 50 9/12/09 A 120 8/12/09 B 80 4/12/09 A 90 Thanks a lot! -- Lowan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Complex Multi-condition, multi-workbook count | Excel Discussion (Misc queries) | |||
Rank by Multi Condition | Excel Discussion (Misc queries) | |||
Help with Multi-Condition Formula | Excel Worksheet Functions | |||
How can I calculate amount of time left based on amount spent? | Excel Worksheet Functions | |||
Multi-condition vlookup | Excel Worksheet Functions |