Sum of data on a per month basis

[Dano]

I can't figure out how to do this. I think SUMPRODUCT might be the
key but I can not figure it out.

I need to extract the monthly sums of expenses incurred from a range
of data.

The data set has two columns Date and Price.

The data changes throughout the year and I have had to adjust the sums
ranges by hand and it has led to some human error on my part.

Using functions I want to be able to automatically get total each
month without manually having to adjust the ranges in the data set.

I am using Excel 2007.


Thanks so much.

 

[Pete_UK]

Suppose your dates are in column D and your expenses in column E.
Elsewhere on the sheet you could list the month and year in a column
(eg starting in X1) like this:

Jan-09
Feb-09
Mar-09

Then in Y1 you could have this formula:

=SUMPRODUCT(--(TEXT(D$1$100,"mmm-yy")=X1),E$1:E$100)

then copy it down as far as you need to. I've assumed 100 rows of
data, so adjust this if you have more.

Hope this helps.

Pete

 

[Rio]

Hi, I am trying to get sum of sales data ( in one column) based on dates
(other column), if any once can help. Will appreciate.

My data in excel looks in following format:
A B
20-Jun-09 $300.00
10-Jun-09 $500.00
01-Sep-09 $1200.00
03-Sep-09 $100.00

Want to get monthly (A column) sum of B column.

Regards

 

[Ron Rosenfeld]

Hi, I am trying to get sum of sales data ( in one column) based on dates
(other column), if any once can help. Will appreciate.

My data in excel looks in following format:
A B
20-Jun-09 $300.00
10-Jun-09 $500.00
01-Sep-09 $1200.00
03-Sep-09 $100.00

Want to get monthly (A column) sum of B column.

Regards

One approach would be to use a pivot table. With the dates in the Rows area,
sales in the data area, you could select the date and group by months, or
months and years.
--ron

 

[Rio]

Hi, I am trying to get monthly sum of sales data ( in one column=A) based on
dates
(other column=B), if any once can help. Will appreciate.
My data in excel looks in following format:
A B
20-Jun-09 $300.00
10-Jun-09 $500.00
01-Sep-09 $1200.00
03-Sep-09 $100.00

E.G. Sum/Total of June = $800 &
Sum/Total of Sep = $1300

Want to get sales sum of months JUNE and SEPTEMBER from B column. Using
Excel 2003.

Regards

 

[Gord Dibben]

Looks like a Pivot Table would do what you want.


Gord Dibben MS Excel MVP