sumtotal as per date range
 

How to sum up the total of a particular column according to date range. For
example all data for the month of Jan 2006. Below is sample worksheet.

A B C
Date Item Amount
01/01/06 xxxxxxxx 100.00
01/08/06 yyyyyyyy 200.00
01/15/06 zzzzzzzz 150.00
01/22/06 mmmm 250.00
01/27/06 nnnnnnn 150.00
02/01/06 ddddddd 225.00
02/08/06 cccccccc 275.00
03/04/06 aaaaaaa 225.00

sumtotal as per date range
 

=SUMPRODUCT(--(YEAR(A2:A200)=2006),--(MONTH(A2:A200)=1),C2:C200)

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"John Abetong" wrote in message
...
How to sum up the total of a particular column according to date range.
For
example all data for the month of Jan 2006. Below is sample worksheet.

A B C
Date Item Amount
01/01/06 xxxxxxxx 100.00
01/08/06 yyyyyyyy 200.00
01/15/06 zzzzzzzz 150.00
01/22/06 mmmm 250.00
01/27/06 nnnnnnn 150.00
02/01/06 ddddddd 225.00
02/08/06 cccccccc 275.00
03/04/06 aaaaaaa 225.00

sumtotal as per date range
 

Bob,

The function below is EXCELLENT.

What if I will sumproduct for a particular item only in Column B?

"Bob Phillips" wrote:

=SUMPRODUCT(--(YEAR(A2:A200)=2006),--(MONTH(A2:A200)=1),C2:C200)

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"John Abetong" wrote in message
...
How to sum up the total of a particular column according to date range.
For
example all data for the month of Jan 2006. Below is sample worksheet.

A B C
Date Item Amount
01/01/06 xxxxxxxx 100.00
01/08/06 yyyyyyyy 200.00
01/15/06 zzzzzzzz 150.00
01/22/06 mmmm 250.00
01/27/06 nnnnnnn 150.00
02/01/06 ddddddd 225.00
02/08/06 cccccccc 275.00
03/04/06 aaaaaaa 225.00

sumtotal as per date range
 

Just keep adding your requirements:

=SUMPRODUCT(--(YEAR(A2:A200)=2006),--(MONTH(A2:A200)=1),
--(b2:b200="something"),C2:C200)

or

=SUMPRODUCT(--(text(a2:a200,"yyyymm")="200601"),--(b2:b200="something"),C2:C200)

John Abetong wrote:

Bob,

The function below is EXCELLENT.

What if I will sumproduct for a particular item only in Column B?

"Bob Phillips" wrote:

=SUMPRODUCT(--(YEAR(A2:A200)=2006),--(MONTH(A2:A200)=1),C2:C200)

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"John Abetong" wrote in message
...
How to sum up the total of a particular column according to date range.
For
example all data for the month of Jan 2006. Below is sample worksheet.

A B C
Date Item Amount
01/01/06 xxxxxxxx 100.00
01/08/06 yyyyyyyy 200.00
01/15/06 zzzzzzzz 150.00
01/22/06 mmmm 250.00
01/27/06 nnnnnnn 150.00
02/01/06 ddddddd 225.00
02/08/06 cccccccc 275.00
03/04/06 aaaaaaa 225.00




--

Dave Peterson

sumtotal as per date range
 

=SUMPRODUCT(--(YEAR(A2:A200)=2006),--(MONTH(A2:A200)=1),--(B2:B200="item_val
ue"),C2:C200)

if the item is numeric, drop the quotation marks.


--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"John Abetong" wrote in message
...
Bob,

The function below is EXCELLENT.

What if I will sumproduct for a particular item only in Column B?

"Bob Phillips" wrote:

=SUMPRODUCT(--(YEAR(A2:A200)=2006),--(MONTH(A2:A200)=1),C2:C200)

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"John Abetong" wrote in message
...
How to sum up the total of a particular column according to date
range.
For
example all data for the month of Jan 2006. Below is sample worksheet.

A B C
Date Item Amount
01/01/06 xxxxxxxx 100.00
01/08/06 yyyyyyyy 200.00
01/15/06 zzzzzzzz 150.00
01/22/06 mmmm 250.00
01/27/06 nnnnnnn 150.00
02/01/06 ddddddd 225.00
02/08/06 cccccccc 275.00
03/04/06 aaaaaaa 225.00