sumtotal as per date range

J

John Abetong

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
 
B

Bob Phillips

=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)
 
J

John Abetong

Bob,

The function below is EXCELLENT.

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

Dave Peterson

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)
 
B

Bob Phillips

=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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top