| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Help
I am currently using the formula
=SUMPRODUCT((MAR_06!$C$2:$C$20000=$A70)*(MAR_06!$J $2:$J$200000)*MAR_06!$J$2:$J$20000) Where Mar_06 = month ( sperate sheet) What I would like is a formula that looks at column "c" and sum $$ by the different months Thanks ce |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula Help
Something like MONTH($C$2:$C$20000=3) would return the sum of all March values.
So, try something along the lines of =SUMPRODUCT(MONTH($C$2:$C$20000=3)[rest of formula]) Dave -- Brevity is the soul of wit. "Curtis" wrote: I am currently using the formula =SUMPRODUCT((MAR_06!$C$2:$C$20000=$A70)*(MAR_06!$J $2:$J$200000)*MAR_06!$J$2:$J$20000) Where Mar_06 = month ( sperate sheet) What I would like is a formula that looks at column "c" and sum $$ by the different months Thanks ce |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula Help
Thanks Dave
The formula now looks like this =SUMPRODUCT(MONTH(Data!$C$2:$C$20000=2)*(Data!$C$2 :$C$20000=$A12)*(Data!$J$2:$J$200000)*Data!$J$2:$ J$20000) However it reurns the same value for all months and iresepctive of refernece $a12, $b12, etc... Thanks "Dave F" wrote: Something like MONTH($C$2:$C$20000=3) would return the sum of all March values. So, try something along the lines of =SUMPRODUCT(MONTH($C$2:$C$20000=3)[rest of formula]) Dave -- Brevity is the soul of wit. "Curtis" wrote: I am currently using the formula =SUMPRODUCT((MAR_06!$C$2:$C$20000=$A70)*(MAR_06!$J $2:$J$200000)*MAR_06!$J$2:$J$20000) Where Mar_06 = month ( sperate sheet) What I would like is a formula that looks at column "c" and sum $$ by the different months Thanks ce |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula Help
Sorry Formual typo but the reults are still the same
=SUMPRODUCT(MONTH(Data!$B$2:$B$20000=3)*(Data!$C$2 :$C$20000=$A12)*(Data!$J$2:$J$200000)*Data!$J$2:$ J$20000) "Curtis" wrote: Thanks Dave The formula now looks like this =SUMPRODUCT(MONTH(Data!$C$2:$C$20000=2)*(Data!$C$2 :$C$20000=$A12)*(Data!$J$2:$J$200000)*Data!$J$2:$ J$20000) However it reurns the same value for all months and iresepctive of refernece $a12, $b12, etc... Thanks "Dave F" wrote: Something like MONTH($C$2:$C$20000=3) would return the sum of all March values. So, try something along the lines of =SUMPRODUCT(MONTH($C$2:$C$20000=3)[rest of formula]) Dave -- Brevity is the soul of wit. "Curtis" wrote: I am currently using the formula =SUMPRODUCT((MAR_06!$C$2:$C$20000=$A70)*(MAR_06!$J $2:$J$200000)*MAR_06!$J$2:$J$20000) Where Mar_06 = month ( sperate sheet) What I would like is a formula that looks at column "c" and sum $$ by the different months Thanks ce |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula Help
hi Curtis,
I guess you need to rewrite the formula as: =SUMPRODUCT((MONTH(Data!$B$2:$B$20000=3)*(Data!$C$ 2:$C$20000=$A12)*(Data!$J$2:$J$200000)*(Data!$J$2 :$J$20000))) Just use the brackets properly. I hope that will work for you. Thanks, Shail Curtis wrote: Sorry Formual typo but the reults are still the same =SUMPRODUCT(MONTH(Data!$B$2:$B$20000=3)*(Data!$C$2 :$C$20000=$A12)*(Data!$J$2:$J$200000)*Data!$J$2:$ J$20000) "Curtis" wrote: Thanks Dave The formula now looks like this =SUMPRODUCT(MONTH(Data!$C$2:$C$20000=2)*(Data!$C$2 :$C$20000=$A12)*(Data!$J$2:$J$200000)*Data!$J$2:$ J$20000) However it reurns the same value for all months and iresepctive of refernece $a12, $b12, etc... Thanks "Dave F" wrote: Something like MONTH($C$2:$C$20000=3) would return the sum of all March values. So, try something along the lines of =SUMPRODUCT(MONTH($C$2:$C$20000=3)[rest of formula]) Dave -- Brevity is the soul of wit. "Curtis" wrote: I am currently using the formula =SUMPRODUCT((MAR_06!$C$2:$C$20000=$A70)*(MAR_06!$J $2:$J$200000)*MAR_06!$J$2:$J$20000) Where Mar_06 = month ( sperate sheet) What I would like is a formula that looks at column "c" and sum $$ by the different months Thanks ce |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula Help
Still does not respect the month. It produces the same value irrespectve of
whether SUMPRODUCT((MONTH(Data!$B$2:$B$20000=3) or SUMPRODUCT((MONTH(Data!$B$2:$B$20000=4) Thanks ce "shail" wrote: hi Curtis, I guess you need to rewrite the formula as: =SUMPRODUCT((MONTH(Data!$B$2:$B$20000=3)*(Data!$C$ 2:$C$20000=$A12)*(Data!$J$2:$J$200000)*(Data!$J$2 :$J$20000))) Just use the brackets properly. I hope that will work for you. Thanks, Shail Curtis wrote: Sorry Formual typo but the reults are still the same =SUMPRODUCT(MONTH(Data!$B$2:$B$20000=3)*(Data!$C$2 :$C$20000=$A12)*(Data!$J$2:$J$200000)*Data!$J$2:$ J$20000) "Curtis" wrote: Thanks Dave The formula now looks like this =SUMPRODUCT(MONTH(Data!$C$2:$C$20000=2)*(Data!$C$2 :$C$20000=$A12)*(Data!$J$2:$J$200000)*Data!$J$2:$ J$20000) However it reurns the same value for all months and iresepctive of refernece $a12, $b12, etc... Thanks "Dave F" wrote: Something like MONTH($C$2:$C$20000=3) would return the sum of all March values. So, try something along the lines of =SUMPRODUCT(MONTH($C$2:$C$20000=3)[rest of formula]) Dave -- Brevity is the soul of wit. "Curtis" wrote: I am currently using the formula =SUMPRODUCT((MAR_06!$C$2:$C$20000=$A70)*(MAR_06!$J $2:$J$200000)*MAR_06!$J$2:$J$20000) Where Mar_06 = month ( sperate sheet) What I would like is a formula that looks at column "c" and sum $$ by the different months Thanks ce |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Match then lookup | Excel Worksheet Functions | |||
| Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
| Formula checking multiple worksheets | Excel Worksheet Functions | |||
| Creating a check mark box | Setting up and Configuration of Excel | |||
| Match / Vlookup within an Array formula | Excel Discussion (Misc queries) | |||
Linear Mode
