Sumif Using Date ranges
 

OS: Windows XP Professional
Ver: Office 2000

I have two worksheets. The first worksheet has daily information where
each row represents a single day, the first column holds the label for
that day. (Hope example shows up correctly)

SHEET 1
A B C D E F G H
01/03/07 Wed 24,524 15,104 4,570 2,088 9,045 86.18%
01/04/07 Thu 55,823 40,528 10,737 4,956 8,711 87.77%
01/05/07 Fri 78,211 58,089 7,731 3,893 6,823 93.30%
01/08/07 Mon 50,584 36,082 16,478 8,612 4,931 76.13%
01/09/07 Tue 54,437 34,800 5,840 2,725 7,498 92.17%



The second worksheet is to be a monthly totals page. It will basically
be an exact duplicate of the first sheet, except each row will represent
an entire month and column A will hold the labels accordingly.

SHEET 2
A B C
Jan-07 sum column C of sheet 1 where
month and year of column A
of sheet 1 = month and year
of A for current row
Feb-07
Mar-07


I want to have this worksheet calculate solely using the daily data
entered on sheet 1. The problem I'm having is identifying month AND
year in criteria. This is extremely important because we have a couple
of years of data in the data source and will continue adding to it for
some time.



Any help provided is greatly appreciated!!


Rita Palazzi
Project Engineer /Global Trade Services
FedEx Express

Sumif Using Date ranges
 

try:

=SUMPRODUCT(--(MONTH(Sheet1!A2:A100)=1),--(YEAR(Sheet1!A2:A100)=2007),sheet1!C2:C100)

for January 2007

HTH

"Rita Palazzi" wrote:

OS: Windows XP Professional
Ver: Office 2000

I have two worksheets. The first worksheet has daily information where
each row represents a single day, the first column holds the label for
that day. (Hope example shows up correctly)

SHEET 1
A B C D E F G H
01/03/07 Wed 24,524 15,104 4,570 2,088 9,045 86.18%
01/04/07 Thu 55,823 40,528 10,737 4,956 8,711 87.77%
01/05/07 Fri 78,211 58,089 7,731 3,893 6,823 93.30%
01/08/07 Mon 50,584 36,082 16,478 8,612 4,931 76.13%
01/09/07 Tue 54,437 34,800 5,840 2,725 7,498 92.17%



The second worksheet is to be a monthly totals page. It will basically
be an exact duplicate of the first sheet, except each row will represent
an entire month and column A will hold the labels accordingly.

SHEET 2
A B C
Jan-07 sum column C of sheet 1 where
month and year of column A
of sheet 1 = month and year
of A for current row
Feb-07
Mar-07


I want to have this worksheet calculate solely using the daily data
entered on sheet 1. The problem I'm having is identifying month AND
year in criteria. This is extremely important because we have a couple
of years of data in the data source and will continue adding to it for
some time.



Any help provided is greatly appreciated!!


Rita Palazzi
Project Engineer /Global Trade Services
FedEx Express

Sumif Using Date ranges
 

=sumproduct(--(text(sheet1!$a$1:$a$100,"yyyymm")=text($a2,"yyyym m")),
sheet1!c$1:c$100)

(all one cell)

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

=======
You may want to look at creating a pivottable. You could group by year and
month. After the little learning curve is over, I bet you'll like it much
better.



Rita Palazzi wrote:

OS: Windows XP Professional
Ver: Office 2000

I have two worksheets. The first worksheet has daily information where
each row represents a single day, the first column holds the label for
that day. (Hope example shows up correctly)

SHEET 1
A B C D E F G H
01/03/07 Wed 24,524 15,104 4,570 2,088 9,045 86.18%
01/04/07 Thu 55,823 40,528 10,737 4,956 8,711 87.77%
01/05/07 Fri 78,211 58,089 7,731 3,893 6,823 93.30%
01/08/07 Mon 50,584 36,082 16,478 8,612 4,931 76.13%
01/09/07 Tue 54,437 34,800 5,840 2,725 7,498 92.17%

The second worksheet is to be a monthly totals page. It will basically
be an exact duplicate of the first sheet, except each row will represent
an entire month and column A will hold the labels accordingly.

SHEET 2
A B C
Jan-07 sum column C of sheet 1 where
month and year of column A
of sheet 1 = month and year
of A for current row
Feb-07
Mar-07

I want to have this worksheet calculate solely using the daily data
entered on sheet 1. The problem I'm having is identifying month AND
year in criteria. This is extremely important because we have a couple
of years of data in the data source and will continue adding to it for
some time.

Any help provided is greatly appreciated!!

Rita Palazzi
Project Engineer /Global Trade Services
FedEx Express

--

Dave Peterson

Sumif Using Date ranges
 

Worked like a charm!! Thanks so much. I do like to do pivot tables, but
don't know if I could maintain the same format or alignment. Will give
it a try though...

Thanks again!!!

Rita

Dave Peterson wrote:

=sumproduct(--(text(sheet1!$a$1:$a$100,"yyyymm")=text($a2,"yyyym m")),
sheet1!c$1:c$100)

(all one cell)

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

=======
You may want to look at creating a pivottable. You could group by year and
month. After the little learning curve is over, I bet you'll like it much
better.



Rita Palazzi wrote:

OS: Windows XP Professional
Ver: Office 2000

I have two worksheets. The first worksheet has daily information where
each row represents a single day, the first column holds the label for
that day. (Hope example shows up correctly)

SHEET 1
A B C D E F G H
01/03/07 Wed 24,524 15,104 4,570 2,088 9,045 86.18%
01/04/07 Thu 55,823 40,528 10,737 4,956 8,711 87.77%
01/05/07 Fri 78,211 58,089 7,731 3,893 6,823 93.30%
01/08/07 Mon 50,584 36,082 16,478 8,612 4,931 76.13%
01/09/07 Tue 54,437 34,800 5,840 2,725 7,498 92.17%

The second worksheet is to be a monthly totals page. It will basically
be an exact duplicate of the first sheet, except each row will represent
an entire month and column A will hold the labels accordingly.

SHEET 2
A B C
Jan-07 sum column C of sheet 1 where
month and year of column A
of sheet 1 = month and year
of A for current row
Feb-07
Mar-07

I want to have this worksheet calculate solely using the daily data
entered on sheet 1. The problem I'm having is identifying month AND
year in criteria. This is extremely important because we have a couple
of years of data in the data source and will continue adding to it for
some time.

Any help provided is greatly appreciated!!

Rita Palazzi
Project Engineer /Global Trade Services
FedEx Express