Formula Help

[Curtis]

I am currently using the formula
=SUMPRODUCT((MAR_06!$C$2:$C$20000=$A70)*(MAR_06!$J$2:$J$20000>0)*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

 

[Dave F]

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

 

[Curtis]

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$20000>0)*Data!$J$2:$J$20000)

However it reurns the same value for all months and iresepctive of refernece
$a12, $b12, etc...

Thanks

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.

I am currently using the formula
=SUMPRODUCT((MAR_06!$C$2:$C$20000=$A70)*(MAR_06!$J$2:$J$20000>0)*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

 

[Curtis]

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$20000>0)*Data!$J$2:$J$20000)

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$20000>0)*Data!$J$2:$J$20000)

However it reurns the same value for all months and iresepctive of refernece
$a12, $b12, etc...

Thanks

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.

I am currently using the formula
=SUMPRODUCT((MAR_06!$C$2:$C$20000=$A70)*(MAR_06!$J$2:$J$20000>0)*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

 

[shail]

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$20000>0)*(Data!$J$2:$J$20000)))

Just use the brackets properly.

I hope that will work for you.

Thanks,

Shail

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$20000>0)*Data!$J$2:$J$20000)

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$20000>0)*Data!$J$2:$J$20000)

However it reurns the same value for all months and iresepctive of refernece
$a12, $b12, etc...

Thanks

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.


:

I am currently using the formula
=SUMPRODUCT((MAR_06!$C$2:$C$20000=$A70)*(MAR_06!$J$2:$J$20000>0)*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

 

[Curtis]

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

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$20000>0)*(Data!$J$2:$J$20000)))

Just use the brackets properly.

I hope that will work for you.

Thanks,

Shail

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$20000>0)*Data!$J$2:$J$20000)

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$20000>0)*Data!$J$2:$J$20000)

However it reurns the same value for all months and iresepctive of refernece
$a12, $b12, etc...

Thanks

:

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.


:

I am currently using the formula
=SUMPRODUCT((MAR_06!$C$2:$C$20000=$A70)*(MAR_06!$J$2:$J$20000>0)*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