Looking for certain months and summing the totals

S

sherobot

Hi
I need a formula to first look for all of january date, febuary dates, etc.
and total them under check amount column. Hope this is clear.thank you

Amount Date Check Amount Month
$320 1/29/2008 $1,886.24 Jan
$40 1/29/2008 Feb
$140 1/29/2008 Mar
$80 1/29/2008 Apr
$80 1/29/2008 May
$1,200 1/29/2008 Jun
$280 2/30/2008 Jul
$1,200 2/31/2008 Aug
$80 3/1/2008 Sep
 
D

Don Guillett

=sumproduct((month(b2:b22)=1)*c2:c22)
to add the year
=sumproduct((year(b2:b22)=2008)*(month(b2:b22)=1)*c2:c22)
 
D

Don Guillett

I used c as your values column when it should have been col A=sumproduct((month(b2:b22)=1)*A2:A22)
 
S

sherobot

hmmm still getting the value error. not sure what I'm doing wrong.
so you are taking the =sumproduct((month(b2:b9)=1)*A2:A9)
where column b has the date and column a has the amount right?
Amount Date
$320 1/29/2008
$40 1/29/2008
$140 1/29/2008
$80 1/29/2008
$80 1/29/2008
$1,200 1/29/2008
$280 2/30/2008
$1,200 2/31/2008
$80 3/1/2008
 
D

Don Guillett

Or, your dates are not dates, but text

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Maybe your dollar amounts are text values and not proper numbers.

Pete
 
S

sherobot

Double checked and they are accounting. Changed it to currency and still have
the error. I also checked the date column. It's listed as date. Still getting
the value error
 
S

sherobot

ok I just did it on a new worksheet and threw in random numbers and dates and
it works perfectly! So it must be something wrong with the data on my sheet.
But now that I know it works, I'll start with a clean worksheet. Hopefully
copy and paste it but if not I can type it in (it's not a lot) Anyway THANK
gUYS!!!!!! it helped a bunch!!!
 
S

sherobot

OK you know what it was...wow this is embarassing.
I had 2/30/08 or 2/31/08. I put in actual dates and it's fine. btw 29 days
in feb this year because it's a leap year. wow sorry to have wasted so much
of your time. :(
 

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

Similar Threads

Help on Pivot Table 2
Specify columns to add for months MTD Diff 3
counting items within a date range 1
Calculate End Date Using Start Date and No. of Days excluding specific days. 0
Specify a column by the month to calculate it's data 5
Summing mulitple line items within date ranges 1
When building a chart Dates are been included that are not in thedate range 1
COUNTIF? SUMIF? SUMPRODUCT? IF? 2

Top