summing

[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.

 

[Pete_UK]

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

 

[Mike H]

Hi,

Try this

=SUMPRODUCT((MONTH(A1:A10)=12)*(YEAR(A1:A10)=2009)*(B1:B10))

Mike

 

[captsamm(remove)]

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

 

[T. Valko]

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

Try this...

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

--
Biff
Microsoft Excel MVP


"captsamm(remove)@comcast.net"