SUMIF for Dates / Months ?

Hi!

Try this:

Make a list of the month names:

Jan
Feb
Mar
...
Dec

Assume the dates are in column A, A1:A365 with no empty cells within the
range.

In the cell beside JAN enter this formula and copy down to DEC:

=SUMPRODUCT(--(MONTH(A$1:A$365)=ROW(1:1)),B$1:B$365)

Biff

"ZMAN" <[email protected]> wrote in message
news:[email protected]...
> Hi, I have a spreadsheet that has every day of the year (1/1/2004 to
> 12/31/2004) and I am trying to sum a column next to it to get only
> totals for each month. Basically I want to know what I get as a sum
> for all of January, February, etc.
>
> So I am trying to use SUMIF in combination with Month(A1:A365)=1
> through =12 and just sum those that are in month 1 and then those in
> month 2, etc.
>
> Column A has all dates and column B has revenue numbers ($ made that
> day)
>
> Can anyone suggest how to do it right?
>
> I was trying arrays like:
> for February: {=SUM(IF((MONTH(A:A)=2),1,0)*(B:B))}
>
> I also tried SUMIF such as:
> example February: =SUMIF(A:A,MONTH(A:A)&">"&"2",B:B)
>
> I know I am doing something utterly wrong but can you please help?
>
> Thanks!
>

Hi, what is "--" before the MONTH function? Do I enter it just like
that? Also, is this an array or a regular function?

Finally, I tried it every way and it won't work - gives either a 0 or a
#NUM! error.





Rowan wrote:
> One way:
>
> =SUMPRODUCT(--(MONTH(A1:A365)=2),B1:B365)
>
> Hope this helps
> Rowan
>
> "ZMAN" wrote:
>
> > Hi, I have a spreadsheet that has every day of the year (1/1/2004 to
> > 12/31/2004) and I am trying to sum a column next to it to get only
> > totals for each month. Basically I want to know what I get as a sum
> > for all of January, February, etc.
> >
> > So I am trying to use SUMIF in combination with Month(A1:A365)=1
> > through =12 and just sum those that are in month 1 and then those in
> > month 2, etc.
> >
> > Column A has all dates and column B has revenue numbers ($ made that
> > day)
> >
> > Can anyone suggest how to do it right?
> >
> > I was trying arrays like:
> > for February: {=SUM(IF((MONTH(A:A)=2),1,0)*(B:B))}
> >
> > I also tried SUMIF such as:
> > example February: =SUMIF(A:A,MONTH(A:A)&">"&"2",B:B)
> >
> > I know I am doing something utterly wrong but can you please help?
> >
> > Thanks!
> >
> >

Fill in the first day dates involving the months of the year of interest
in column C from C1 on:

1-Jan-04
1-Feb-04
1-Mar-04
etc.

In D1 enter & copy down:

=EOMONTH(C1,0)

In E1 enter & copy down:

=SUMIF(A:A,">="&C1,B:B)-SUMIF(A:A,">"&D1,B:B)

ZMAN wrote:
> Hi, I have a spreadsheet that has every day of the year (1/1/2004 to
> 12/31/2004) and I am trying to sum a column next to it to get only
> totals for each month. Basically I want to know what I get as a sum
> for all of January, February, etc.
>
> So I am trying to use SUMIF in combination with Month(A1:A365)=1
> through =12 and just sum those that are in month 1 and then those in
> month 2, etc.
>
> Column A has all dates and column B has revenue numbers ($ made that
> day)
>
> Can anyone suggest how to do it right?
>
> I was trying arrays like:
> for February: {=SUM(IF((MONTH(A:A)=2),1,0)*(B:B))}
>
> I also tried SUMIF such as:
> example February: =SUMIF(A:A,MONTH(A:A)&">"&"2",B:B)
>
> I know I am doing something utterly wrong but can you please help?
>
> Thanks!
>

One way:

=SUMPRODUCT(--(MONTH(A1:A365)=2),B1:B365)

Hope this helps
Rowan

"ZMAN" wrote:

> Hi, I have a spreadsheet that has every day of the year (1/1/2004 to
> 12/31/2004) and I am trying to sum a column next to it to get only
> totals for each month. Basically I want to know what I get as a sum
> for all of January, February, etc.
>
> So I am trying to use SUMIF in combination with Month(A1:A365)=1
> through =12 and just sum those that are in month 1 and then those in
> month 2, etc.
>
> Column A has all dates and column B has revenue numbers ($ made that
> day)
>
> Can anyone suggest how to do it right?
>
> I was trying arrays like:
> for February: {=SUM(IF((MONTH(A:A)=2),1,0)*(B:B))}
>
> I also tried SUMIF such as:
> example February: =SUMIF(A:A,MONTH(A:A)&">"&"2",B:B)
>
> I know I am doing something utterly wrong but can you please help?
>
> Thanks!
>
>

One way:

=SUMPRODUCT(--(MONTH(A1:A365)=2),B1:B365)

Hope this helps
Rowan

"ZMAN" wrote:

> Hi, I have a spreadsheet that has every day of the year (1/1/2004 to
> 12/31/2004) and I am trying to sum a column next to it to get only
> totals for each month. Basically I want to know what I get as a sum
> for all of January, February, etc.
>
> So I am trying to use SUMIF in combination with Month(A1:A365)=1
> through =12 and just sum those that are in month 1 and then those in
> month 2, etc.
>
> Column A has all dates and column B has revenue numbers ($ made that
> day)
>
> Can anyone suggest how to do it right?
>
> I was trying arrays like:
> for February: {=SUM(IF((MONTH(A:A)=2),1,0)*(B:B))}
>
> I also tried SUMIF such as:
> example February: =SUMIF(A:A,MONTH(A:A)&">"&"2",B:B)
>
> I know I am doing something utterly wrong but can you please help?
>
> Thanks!
>
>

Hi, what is "--" before the MONTH function? Do I enter it just like
that? Also, is this an array or a regular function?

Finally, I tried it every way and it won't work - gives either a 0 or a
#NUM! error.





Rowan wrote:
> One way:
>
> =SUMPRODUCT(--(MONTH(A1:A365)=2),B1:B365)
>
> Hope this helps
> Rowan
>
> "ZMAN" wrote:
>
> > Hi, I have a spreadsheet that has every day of the year (1/1/2004 to
> > 12/31/2004) and I am trying to sum a column next to it to get only
> > totals for each month. Basically I want to know what I get as a sum
> > for all of January, February, etc.
> >
> > So I am trying to use SUMIF in combination with Month(A1:A365)=1
> > through =12 and just sum those that are in month 1 and then those in
> > month 2, etc.
> >
> > Column A has all dates and column B has revenue numbers ($ made that
> > day)
> >
> > Can anyone suggest how to do it right?
> >
> > I was trying arrays like:
> > for February: {=SUM(IF((MONTH(A:A)=2),1,0)*(B:B))}
> >
> > I also tried SUMIF such as:
> > example February: =SUMIF(A:A,MONTH(A:A)&">"&"2",B:B)
> >
> > I know I am doing something utterly wrong but can you please help?
> >
> > Thanks!
> >
> >

Hi!

Try this:

Make a list of the month names:

Jan
Feb
Mar
...
Dec

Assume the dates are in column A, A1:A365 with no empty cells within the
range.

In the cell beside JAN enter this formula and copy down to DEC:

=SUMPRODUCT(--(MONTH(A$1:A$365)=ROW(1:1)),B$1:B$365)

Biff

"ZMAN" <[email protected]> wrote in message
news:[email protected]...
> Hi, I have a spreadsheet that has every day of the year (1/1/2004 to
> 12/31/2004) and I am trying to sum a column next to it to get only
> totals for each month. Basically I want to know what I get as a sum
> for all of January, February, etc.
>
> So I am trying to use SUMIF in combination with Month(A1:A365)=1
> through =12 and just sum those that are in month 1 and then those in
> month 2, etc.
>
> Column A has all dates and column B has revenue numbers ($ made that
> day)
>
> Can anyone suggest how to do it right?
>
> I was trying arrays like:
> for February: {=SUM(IF((MONTH(A:A)=2),1,0)*(B:B))}
>
> I also tried SUMIF such as:
> example February: =SUMIF(A:A,MONTH(A:A)&">"&"2",B:B)
>
> I know I am doing something utterly wrong but can you please help?
>
> Thanks!
>

Fill in the first day dates involving the months of the year of interest
in column C from C1 on:

1-Jan-04
1-Feb-04
1-Mar-04
etc.

In D1 enter & copy down:

=EOMONTH(C1,0)

In E1 enter & copy down:

=SUMIF(A:A,">="&C1,B:B)-SUMIF(A:A,">"&D1,B:B)

ZMAN wrote:
> Hi, I have a spreadsheet that has every day of the year (1/1/2004 to
> 12/31/2004) and I am trying to sum a column next to it to get only
> totals for each month. Basically I want to know what I get as a sum
> for all of January, February, etc.
>
> So I am trying to use SUMIF in combination with Month(A1:A365)=1
> through =12 and just sum those that are in month 1 and then those in
> month 2, etc.
>
> Column A has all dates and column B has revenue numbers ($ made that
> day)
>
> Can anyone suggest how to do it right?
>
> I was trying arrays like:
> for February: {=SUM(IF((MONTH(A:A)=2),1,0)*(B:B))}
>
> I also tried SUMIF such as:
> example February: =SUMIF(A:A,MONTH(A:A)&">"&"2",B:B)
>
> I know I am doing something utterly wrong but can you please help?
>
> Thanks!
>

One way:

=SUMPRODUCT(--(MONTH(A1:A365)=2),B1:B365)

Hope this helps
Rowan

"ZMAN" wrote:

> Hi, I have a spreadsheet that has every day of the year (1/1/2004 to
> 12/31/2004) and I am trying to sum a column next to it to get only
> totals for each month. Basically I want to know what I get as a sum
> for all of January, February, etc.
>
> So I am trying to use SUMIF in combination with Month(A1:A365)=1
> through =12 and just sum those that are in month 1 and then those in
> month 2, etc.
>
> Column A has all dates and column B has revenue numbers ($ made that
> day)
>
> Can anyone suggest how to do it right?
>
> I was trying arrays like:
> for February: {=SUM(IF((MONTH(A:A)=2),1,0)*(B:B))}
>
> I also tried SUMIF such as:
> example February: =SUMIF(A:A,MONTH(A:A)&">"&"2",B:B)
>
> I know I am doing something utterly wrong but can you please help?
>
> Thanks!
>
>

Hi, what is "--" before the MONTH function? Do I enter it just like
that? Also, is this an array or a regular function?

Finally, I tried it every way and it won't work - gives either a 0 or a
#NUM! error.





Rowan wrote:
> One way:
>
> =SUMPRODUCT(--(MONTH(A1:A365)=2),B1:B365)
>
> Hope this helps
> Rowan
>
> "ZMAN" wrote:
>
> > Hi, I have a spreadsheet that has every day of the year (1/1/2004 to
> > 12/31/2004) and I am trying to sum a column next to it to get only
> > totals for each month. Basically I want to know what I get as a sum
> > for all of January, February, etc.
> >
> > So I am trying to use SUMIF in combination with Month(A1:A365)=1
> > through =12 and just sum those that are in month 1 and then those in
> > month 2, etc.
> >
> > Column A has all dates and column B has revenue numbers ($ made that
> > day)
> >
> > Can anyone suggest how to do it right?
> >
> > I was trying arrays like:
> > for February: {=SUM(IF((MONTH(A:A)=2),1,0)*(B:B))}
> >
> > I also tried SUMIF such as:
> > example February: =SUMIF(A:A,MONTH(A:A)&">"&"2",B:B)
> >
> > I know I am doing something utterly wrong but can you please help?
> >
> > Thanks!
> >
> >

Fill in the first day dates involving the months of the year of interest
in column C from C1 on:

1-Jan-04
1-Feb-04
1-Mar-04
etc.

In D1 enter & copy down:

=EOMONTH(C1,0)

In E1 enter & copy down:

=SUMIF(A:A,">="&C1,B:B)-SUMIF(A:A,">"&D1,B:B)

ZMAN wrote:
> Hi, I have a spreadsheet that has every day of the year (1/1/2004 to
> 12/31/2004) and I am trying to sum a column next to it to get only
> totals for each month. Basically I want to know what I get as a sum
> for all of January, February, etc.
>
> So I am trying to use SUMIF in combination with Month(A1:A365)=1
> through =12 and just sum those that are in month 1 and then those in
> month 2, etc.
>
> Column A has all dates and column B has revenue numbers ($ made that
> day)
>
> Can anyone suggest how to do it right?
>
> I was trying arrays like:
> for February: {=SUM(IF((MONTH(A:A)=2),1,0)*(B:B))}
>
> I also tried SUMIF such as:
> example February: =SUMIF(A:A,MONTH(A:A)&">"&"2",B:B)
>
> I know I am doing something utterly wrong but can you please help?
>
> Thanks!
>

Hi!

Try this:

Make a list of the month names:

Jan
Feb
Mar
...
Dec

Assume the dates are in column A, A1:A365 with no empty cells within the
range.

In the cell beside JAN enter this formula and copy down to DEC:

=SUMPRODUCT(--(MONTH(A$1:A$365)=ROW(1:1)),B$1:B$365)

Biff

"ZMAN" <[email protected]> wrote in message
news:[email protected]...
> Hi, I have a spreadsheet that has every day of the year (1/1/2004 to
> 12/31/2004) and I am trying to sum a column next to it to get only
> totals for each month. Basically I want to know what I get as a sum
> for all of January, February, etc.
>
> So I am trying to use SUMIF in combination with Month(A1:A365)=1
> through =12 and just sum those that are in month 1 and then those in
> month 2, etc.
>
> Column A has all dates and column B has revenue numbers ($ made that
> day)
>
> Can anyone suggest how to do it right?
>
> I was trying arrays like:
> for February: {=SUM(IF((MONTH(A:A)=2),1,0)*(B:B))}
>
> I also tried SUMIF such as:
> example February: =SUMIF(A:A,MONTH(A:A)&">"&"2",B:B)
>
> I know I am doing something utterly wrong but can you please help?
>
> Thanks!
>

Fill in the first day dates involving the months of the year of interest
in column C from C1 on:

1-Jan-04
1-Feb-04
1-Mar-04
etc.

In D1 enter & copy down:

=EOMONTH(C1,0)

In E1 enter & copy down:

=SUMIF(A:A,">="&C1,B:B)-SUMIF(A:A,">"&D1,B:B)

ZMAN wrote:
> Hi, I have a spreadsheet that has every day of the year (1/1/2004 to
> 12/31/2004) and I am trying to sum a column next to it to get only
> totals for each month. Basically I want to know what I get as a sum
> for all of January, February, etc.
>
> So I am trying to use SUMIF in combination with Month(A1:A365)=1
> through =12 and just sum those that are in month 1 and then those in
> month 2, etc.
>
> Column A has all dates and column B has revenue numbers ($ made that
> day)
>
> Can anyone suggest how to do it right?
>
> I was trying arrays like:
> for February: {=SUM(IF((MONTH(A:A)=2),1,0)*(B:B))}
>
> I also tried SUMIF such as:
> example February: =SUMIF(A:A,MONTH(A:A)&">"&"2",B:B)
>
> I know I am doing something utterly wrong but can you please help?
>
> Thanks!
>

Hi!

Try this:

Make a list of the month names:

Jan
Feb
Mar
...
Dec

Assume the dates are in column A, A1:A365 with no empty cells within the
range.

In the cell beside JAN enter this formula and copy down to DEC:

=SUMPRODUCT(--(MONTH(A$1:A$365)=ROW(1:1)),B$1:B$365)

Biff

"ZMAN" <[email protected]> wrote in message
news:[email protected]...
> Hi, I have a spreadsheet that has every day of the year (1/1/2004 to
> 12/31/2004) and I am trying to sum a column next to it to get only
> totals for each month. Basically I want to know what I get as a sum
> for all of January, February, etc.
>
> So I am trying to use SUMIF in combination with Month(A1:A365)=1
> through =12 and just sum those that are in month 1 and then those in
> month 2, etc.
>
> Column A has all dates and column B has revenue numbers ($ made that
> day)
>
> Can anyone suggest how to do it right?
>
> I was trying arrays like:
> for February: {=SUM(IF((MONTH(A:A)=2),1,0)*(B:B))}
>
> I also tried SUMIF such as:
> example February: =SUMIF(A:A,MONTH(A:A)&">"&"2",B:B)
>
> I know I am doing something utterly wrong but can you please help?
>
> Thanks!
>

One way:

=SUMPRODUCT(--(MONTH(A1:A365)=2),B1:B365)

Hope this helps
Rowan

"ZMAN" wrote:

> Hi, I have a spreadsheet that has every day of the year (1/1/2004 to
> 12/31/2004) and I am trying to sum a column next to it to get only
> totals for each month. Basically I want to know what I get as a sum
> for all of January, February, etc.
>
> So I am trying to use SUMIF in combination with Month(A1:A365)=1
> through =12 and just sum those that are in month 1 and then those in
> month 2, etc.
>
> Column A has all dates and column B has revenue numbers ($ made that
> day)
>
> Can anyone suggest how to do it right?
>
> I was trying arrays like:
> for February: {=SUM(IF((MONTH(A:A)=2),1,0)*(B:B))}
>
> I also tried SUMIF such as:
> example February: =SUMIF(A:A,MONTH(A:A)&">"&"2",B:B)
>
> I know I am doing something utterly wrong but can you please help?
>
> Thanks!
>
>

Hi, what is "--" before the MONTH function? Do I enter it just like
that? Also, is this an array or a regular function?

Finally, I tried it every way and it won't work - gives either a 0 or a
#NUM! error.





Rowan wrote:
> One way:
>
> =SUMPRODUCT(--(MONTH(A1:A365)=2),B1:B365)
>
> Hope this helps
> Rowan
>
> "ZMAN" wrote:
>
> > Hi, I have a spreadsheet that has every day of the year (1/1/2004 to
> > 12/31/2004) and I am trying to sum a column next to it to get only
> > totals for each month. Basically I want to know what I get as a sum
> > for all of January, February, etc.
> >
> > So I am trying to use SUMIF in combination with Month(A1:A365)=1
> > through =12 and just sum those that are in month 1 and then those in
> > month 2, etc.
> >
> > Column A has all dates and column B has revenue numbers ($ made that
> > day)
> >
> > Can anyone suggest how to do it right?
> >
> > I was trying arrays like:
> > for February: {=SUM(IF((MONTH(A:A)=2),1,0)*(B:B))}
> >
> > I also tried SUMIF such as:
> > example February: =SUMIF(A:A,MONTH(A:A)&">"&"2",B:B)
> >
> > I know I am doing something utterly wrong but can you please help?
> >
> > Thanks!
> >
> >

Hi, I have a spreadsheet that has every day of the year (1/1/2004 to
12/31/2004) and I am trying to sum a column next to it to get only
totals for each month. Basically I want to know what I get as a sum
for all of January, February, etc.

So I am trying to use SUMIF in combination with Month(A1:A365)=1
through =12 and just sum those that are in month 1 and then those in
month 2, etc.

Column A has all dates and column B has revenue numbers ($ made that
day)

Can anyone suggest how to do it right?

I was trying arrays like:
for February: {=SUM(IF((MONTH(A:A)=2),1,0)*(B:B))}

I also tried SUMIF such as:
example February: =SUMIF(A:A,MONTH(A:A)&">"&"2",B:B)

I know I am doing something utterly wrong but can you please help?

Thanks!

Fill in the first day dates involving the months of the year of interest
in column C from C1 on:

1-Jan-04
1-Feb-04
1-Mar-04
etc.

In D1 enter & copy down:

=EOMONTH(C1,0)

In E1 enter & copy down:

=SUMIF(A:A,">="&C1,B:B)-SUMIF(A:A,">"&D1,B:B)

ZMAN wrote:
> Hi, I have a spreadsheet that has every day of the year (1/1/2004 to
> 12/31/2004) and I am trying to sum a column next to it to get only
> totals for each month. Basically I want to know what I get as a sum
> for all of January, February, etc.
>
> So I am trying to use SUMIF in combination with Month(A1:A365)=1
> through =12 and just sum those that are in month 1 and then those in
> month 2, etc.
>
> Column A has all dates and column B has revenue numbers ($ made that
> day)
>
> Can anyone suggest how to do it right?
>
> I was trying arrays like:
> for February: {=SUM(IF((MONTH(A:A)=2),1,0)*(B:B))}
>
> I also tried SUMIF such as:
> example February: =SUMIF(A:A,MONTH(A:A)&">"&"2",B:B)
>
> I know I am doing something utterly wrong but can you please help?
>
> Thanks!
>

Hi!

Try this:

Make a list of the month names:

Jan
Feb
Mar
...
Dec

Assume the dates are in column A, A1:A365 with no empty cells within the
range.

In the cell beside JAN enter this formula and copy down to DEC:

=SUMPRODUCT(--(MONTH(A$1:A$365)=ROW(1:1)),B$1:B$365)

Biff

"ZMAN" <[email protected]> wrote in message
news:[email protected]...
> Hi, I have a spreadsheet that has every day of the year (1/1/2004 to
> 12/31/2004) and I am trying to sum a column next to it to get only
> totals for each month. Basically I want to know what I get as a sum
> for all of January, February, etc.
>
> So I am trying to use SUMIF in combination with Month(A1:A365)=1
> through =12 and just sum those that are in month 1 and then those in
> month 2, etc.
>
> Column A has all dates and column B has revenue numbers ($ made that
> day)
>
> Can anyone suggest how to do it right?
>
> I was trying arrays like:
> for February: {=SUM(IF((MONTH(A:A)=2),1,0)*(B:B))}
>
> I also tried SUMIF such as:
> example February: =SUMIF(A:A,MONTH(A:A)&">"&"2",B:B)
>
> I know I am doing something utterly wrong but can you please help?
>
> Thanks!
>

Hi, what is "--" before the MONTH function? Do I enter it just like
that? Also, is this an array or a regular function?

Finally, I tried it every way and it won't work - gives either a 0 or a
#NUM! error.





Rowan wrote:
> One way:
>
> =SUMPRODUCT(--(MONTH(A1:A365)=2),B1:B365)
>
> Hope this helps
> Rowan
>
> "ZMAN" wrote:
>
> > Hi, I have a spreadsheet that has every day of the year (1/1/2004 to
> > 12/31/2004) and I am trying to sum a column next to it to get only
> > totals for each month. Basically I want to know what I get as a sum
> > for all of January, February, etc.
> >
> > So I am trying to use SUMIF in combination with Month(A1:A365)=1
> > through =12 and just sum those that are in month 1 and then those in
> > month 2, etc.
> >
> > Column A has all dates and column B has revenue numbers ($ made that
> > day)
> >
> > Can anyone suggest how to do it right?
> >
> > I was trying arrays like:
> > for February: {=SUM(IF((MONTH(A:A)=2),1,0)*(B:B))}
> >
> > I also tried SUMIF such as:
> > example February: =SUMIF(A:A,MONTH(A:A)&">"&"2",B:B)
> >
> > I know I am doing something utterly wrong but can you please help?
> >
> > Thanks!
> >
> >

One way:

=SUMPRODUCT(--(MONTH(A1:A365)=2),B1:B365)

Hope this helps
Rowan

"ZMAN" wrote:

> Hi, I have a spreadsheet that has every day of the year (1/1/2004 to
> 12/31/2004) and I am trying to sum a column next to it to get only
> totals for each month. Basically I want to know what I get as a sum
> for all of January, February, etc.
>
> So I am trying to use SUMIF in combination with Month(A1:A365)=1
> through =12 and just sum those that are in month 1 and then those in
> month 2, etc.
>
> Column A has all dates and column B has revenue numbers ($ made that
> day)
>
> Can anyone suggest how to do it right?
>
> I was trying arrays like:
> for February: {=SUM(IF((MONTH(A:A)=2),1,0)*(B:B))}
>
> I also tried SUMIF such as:
> example February: =SUMIF(A:A,MONTH(A:A)&">"&"2",B:B)
>
> I know I am doing something utterly wrong but can you please help?
>
> Thanks!
>
>