Sum multiple columns with Sumproduct
 

Hi there,

I have searched through the newsgroup to try and find an answer to my
problem but with no results yet.

What I'm looking for is some help regarding the sumproduct function and
using it in conjunction with the offset function.

My particular data set looks something like this (It is much larger...but
you'll get the idea):

A B C D E
Item Category Jan Feb Mar
1 1 5 25 15
1 2 10 15 5
2 1 5 10 20

What I need to come up with is a formula that calcs the month total for a
particular item and category and a yearly total of a particular item and
category.

For the february monthly total, I used a formula like so:

=SUMPRODUCT(--($A$2:$A$4=1),(--($B$2:$B$4=1),OFFSET($B$1,1,2,3,1))

This works fine. But my problem lies when I need to calc a yearly number
consisting of the sum of January AND February. If I expand my offset range
to OFFSET($B$1,1,1,3,2), I get errors.

Would someone be able to help me out??

Thanks,
TT

Sum multiple columns with Sumproduct
 

Jan
=SUMPRODUCT(--($A$2:$A$4=1)*($B$2:$B$4=1)*(OFFSET($C$2,0,0,3,1)) )
Feb
=SUMPRODUCT(--($A$2:$A$4=1)*($B$2:$B$4=1)*(OFFSET($C$2,0,1,3,1)) )
March
=SUMPRODUCT(--($A$2:$A$4=1)*($B$2:$B$4=1)*(OFFSET($C$2,0,2,3,1)) )


Jan+Feb
=SUMPRODUCT(--($A$2:$A$4=1)*($B$2:$B$4=1)*(OFFSET($C$2,0,0,3,2)) )

Jan+Feb+March
=SUMPRODUCT(--($A$2:$A$4=1)*($B$2:$B$4=1)*(OFFSET($C$2,0,0,3,3)) )

HTH

"TT" wrote:

Hi there,

I have searched through the newsgroup to try and find an answer to my
problem but with no results yet.

What I'm looking for is some help regarding the sumproduct function and
using it in conjunction with the offset function.

My particular data set looks something like this (It is much larger...but
you'll get the idea):

A B C D E
Item Category Jan Feb Mar
1 1 5 25 15
1 2 10 15 5
2 1 5 10 20

What I need to come up with is a formula that calcs the month total for a
particular item and category and a yearly total of a particular item and
category.

For the february monthly total, I used a formula like so:

=SUMPRODUCT(--($A$2:$A$4=1),(--($B$2:$B$4=1),OFFSET($B$1,1,2,3,1))

This works fine. But my problem lies when I need to calc a yearly number
consisting of the sum of January AND February. If I expand my offset range
to OFFSET($B$1,1,1,3,2), I get errors.

Would someone be able to help me out??

Thanks,
TT

Sum multiple columns with Sumproduct
 

Thanks very much!

I'll give this a shot and see if I can get it to work.
--
TT


"Toppers" wrote:

Jan
=SUMPRODUCT(--($A$2:$A$4=1)*($B$2:$B$4=1)*(OFFSET($C$2,0,0,3,1)) )
Feb
=SUMPRODUCT(--($A$2:$A$4=1)*($B$2:$B$4=1)*(OFFSET($C$2,0,1,3,1)) )
March
=SUMPRODUCT(--($A$2:$A$4=1)*($B$2:$B$4=1)*(OFFSET($C$2,0,2,3,1)) )


Jan+Feb
=SUMPRODUCT(--($A$2:$A$4=1)*($B$2:$B$4=1)*(OFFSET($C$2,0,0,3,2)) )

Jan+Feb+March
=SUMPRODUCT(--($A$2:$A$4=1)*($B$2:$B$4=1)*(OFFSET($C$2,0,0,3,3)) )

HTH

"TT" wrote:

Hi there,

I have searched through the newsgroup to try and find an answer to my
problem but with no results yet.

What I'm looking for is some help regarding the sumproduct function and
using it in conjunction with the offset function.

My particular data set looks something like this (It is much larger...but
you'll get the idea):

A B C D E
Item Category Jan Feb Mar
1 1 5 25 15
1 2 10 15 5
2 1 5 10 20

What I need to come up with is a formula that calcs the month total for a
particular item and category and a yearly total of a particular item and
category.

For the february monthly total, I used a formula like so:

=SUMPRODUCT(--($A$2:$A$4=1),(--($B$2:$B$4=1),OFFSET($B$1,1,2,3,1))

This works fine. But my problem lies when I need to calc a yearly number
consisting of the sum of January AND February. If I expand my offset range
to OFFSET($B$1,1,1,3,2), I get errors.

Would someone be able to help me out??

Thanks,
TT

Sum multiple columns with Sumproduct
 

Thank you so much for your help. It worked perfectly!!

I had my formula very similar to this before but I was using a comma ","
instead of a multiplication symbol "*".

Do you happen to know what the difference is and why the other version of
the formula wouldn't work??

Thanks again for your help!!

TT



"TT" wrote:

Thanks very much!

I'll give this a shot and see if I can get it to work.
--
TT


"Toppers" wrote:

Jan
=SUMPRODUCT(--($A$2:$A$4=1)*($B$2:$B$4=1)*(OFFSET($C$2,0,0,3,1)) )
Feb
=SUMPRODUCT(--($A$2:$A$4=1)*($B$2:$B$4=1)*(OFFSET($C$2,0,1,3,1)) )
March
=SUMPRODUCT(--($A$2:$A$4=1)*($B$2:$B$4=1)*(OFFSET($C$2,0,2,3,1)) )


Jan+Feb
=SUMPRODUCT(--($A$2:$A$4=1)*($B$2:$B$4=1)*(OFFSET($C$2,0,0,3,2)) )

Jan+Feb+March
=SUMPRODUCT(--($A$2:$A$4=1)*($B$2:$B$4=1)*(OFFSET($C$2,0,0,3,3)) )

HTH

"TT" wrote:

Hi there,

I have searched through the newsgroup to try and find an answer to my
problem but with no results yet.

What I'm looking for is some help regarding the sumproduct function and
using it in conjunction with the offset function.

My particular data set looks something like this (It is much larger...but
you'll get the idea):

A B C D E
Item Category Jan Feb Mar
1 1 5 25 15
1 2 10 15 5
2 1 5 10 20

What I need to come up with is a formula that calcs the month total for a
particular item and category and a yearly total of a particular item and
category.

For the february monthly total, I used a formula like so:

=SUMPRODUCT(--($A$2:$A$4=1),(--($B$2:$B$4=1),OFFSET($B$1,1,2,3,1))

This works fine. But my problem lies when I need to calc a yearly number
consisting of the sum of January AND February. If I expand my offset range
to OFFSET($B$1,1,1,3,2), I get errors.

Would someone be able to help me out??

Thanks,
TT