SUMPRODUCT
 

I have a spreadsheet that looks something like:

Row A 1/1/07 1/2/07 1/3/07 .... 12/31/07
Row B 4 2 5 .... 9

I would like to sum everthing in row B within a certain month. I have tried:

=SUMPRODUCT(Month(A1:A365)=1,B1:B365)

This is not working. Would someone tell me what I'm missing?

Thanks so much.
Ellen

SUMPRODUCT
 

=sumproduct() likes numbers:

=SUMPRODUCT(--(Month(A1:A365)=1),B1:B365)

But be careful. An empty cell will look like it has a month() of january.

=SUMPRODUCT(--(Month(A1:A365)=1),--(a1:a365<""),B1:B365)
might be better.

Ellen G wrote:

I have a spreadsheet that looks something like:

Row A 1/1/07 1/2/07 1/3/07 .... 12/31/07
Row B 4 2 5 .... 9

I would like to sum everthing in row B within a certain month. I have tried:

=SUMPRODUCT(Month(A1:A365)=1,B1:B365)

This is not working. Would someone tell me what I'm missing?

Thanks so much.
Ellen

--

Dave Peterson

SUMPRODUCT
 

Okay -- sorry. I typed the formula in my original message incorrectly. Here
is the type of formula I've actually been using that is not working:

=SUMPRODUCT(Month(A1:Z1)=1,A2:Z2)

What am I doing wrong???

Ellen

"Ellen G" wrote:

I have a spreadsheet that looks something like:

Row A 1/1/07 1/2/07 1/3/07 .... 12/31/07
Row B 4 2 5 .... 9

I would like to sum everthing in row B within a certain month. I have tried:

=SUMPRODUCT(Month(A1:A365)=1,B1:B365)

This is not working. Would someone tell me what I'm missing?

Thanks so much.
Ellen

SUMPRODUCT
 

=SUMPRODUCT(-(Month(A1:Z1)=1,A2:Z2)
or
=SUMPRODUCT((Month(A1:Z1)=1)*A2:Z2)



--
Don Guillett
SalesAid Software

"Ellen G" wrote in message
...
Okay -- sorry. I typed the formula in my original message incorrectly.
Here
is the type of formula I've actually been using that is not working:

=SUMPRODUCT(Month(A1:Z1)=1,A2:Z2)

What am I doing wrong???

Ellen

"Ellen G" wrote:

I have a spreadsheet that looks something like:

Row A 1/1/07 1/2/07 1/3/07 .... 12/31/07
Row B 4 2 5 .... 9

I would like to sum everthing in row B within a certain month. I have
tried:

=SUMPRODUCT(Month(A1:A365)=1,B1:B365)

This is not working. Would someone tell me what I'm missing?

Thanks so much.
Ellen

SUMPRODUCT
 

Thanks so much, Dave. This helped a lot.

Ellen

"Dave Peterson" wrote:

=sumproduct() likes numbers:

=SUMPRODUCT(--(Month(A1:A365)=1),B1:B365)

But be careful. An empty cell will look like it has a month() of january.

=SUMPRODUCT(--(Month(A1:A365)=1),--(a1:a365<""),B1:B365)
might be better.

Ellen G wrote:

I have a spreadsheet that looks something like:

Row A 1/1/07 1/2/07 1/3/07 .... 12/31/07
Row B 4 2 5 .... 9

I would like to sum everthing in row B within a certain month. I have tried:

=SUMPRODUCT(Month(A1:A365)=1,B1:B365)

This is not working. Would someone tell me what I'm missing?

Thanks so much.
Ellen

--

Dave Peterson