summing

  • Thread starter Thread starter captsamm(remove)
  • Start date Start date
C

captsamm(remove)

I have one column of dates and a second column of costs. How would I sum all
of the particular costs for a certain month.
 
Put Jan in D1, say, and this formula in E1:

=SUMPRODUCT(--(TEXT(A1:A1000,"mmm")=D1),B1:B1000)

Or, better still, put 'Jan-09 (with the apostrophe) in D1, and this
formula in E1:

=SUMPRODUCT(--(TEXT(A1:A1000,"mmm-yy")=D1),B1:B1000)

You need to put other months or month-year combinations in cells below
D1, then you can copy the formula in E1 down that column.

Hope this helps.

Pete
 
That's great. However, I would like to do the summing irrespective of the
year. I am trying to show a multi year trend with the data displayed only by
the month. In this case the total number of dollars spent a certain month
with five year spread. I don't care what the year is, only the cumulative
amount that was spent in each of the 12 months. I tried to remove the year
part of the statement but got an error message. I've never done array
calculations before, so consider me ignorant of how they operate.

Thanks

Samm
 
=SUMPRODUCT((MONTH(A1:A10)=12)*(YEAR(A1:A10)=2009)*(B1:B10))
I tried to remove the year part of the
statement but got an error message.
Click to expand...

Try this...

=SUMPRODUCT(--(MONTH(A1:A10)=12),B1:B10)

--
Biff
Microsoft Excel MVP


"captsamm(remove)@comcast.net"
 
Back
Top