I'm working with a spreadsheet that keeps track of checks written to
customers, and need to be able to total the payments for each month. The
spreadsheet begins in Nov, 04, and will continue to grow into the future, so
I'm trying to create a total for each month of each year. I've created a
function to check the date because there are text values within the data that
give errors when I try and run the MONTH and YEAR functions in Excel 2003.
So, I created this function:

Function MonthTotal(MonthNeeded, YearNeeded, VisitDate, Amount)

If VisitDate = "Last Visit Date" Then
MonthTotal = 0
Else
If Month(VisitDate) = MonthNeeded Then
If Year(VisitDate) = YearNeeded Then
MonthTotal = Amount
Else: MonthTotal = 0
End If
Else: MonthTotal = 0
End If
End If

End Function

The function works to check a single data point, but I'm having trouble
using it now to sum up the totals for the month. I thought about trying to
create another funtion that runs a loop through all the data that then calls
this function and keeps a running total, but I'm not very familiar with VB
and don't know the process. Any help as to what I should do would be great.
Thanks a lot!

Have you tried using a pivot table. You can very easily group by year and
month.

"unofarmbob" wrote:

I'm working with a spreadsheet that keeps track of checks written to
customers, and need to be able to total the payments for each month. The
spreadsheet begins in Nov, 04, and will continue to grow into the future, so
I'm trying to create a total for each month of each year. I've created a
function to check the date because there are text values within the data that
give errors when I try and run the MONTH and YEAR functions in Excel 2003.
So, I created this function:

Function MonthTotal(MonthNeeded, YearNeeded, VisitDate, Amount)

If VisitDate = "Last Visit Date" Then
MonthTotal = 0
Else
If Month(VisitDate) = MonthNeeded Then
If Year(VisitDate) = YearNeeded Then
MonthTotal = Amount
Else: MonthTotal = 0
End If
Else: MonthTotal = 0
End If
End If

End Function

The function works to check a single data point, but I'm having trouble
using it now to sum up the totals for the month. I thought about trying to
create another funtion that runs a loop through all the data that then calls
this function and keeps a running total, but I'm not very familiar with VB
and don't know the process. Any help as to what I should do would be great.
Thanks a lot!

I'm not too familiar with pivot tables, and the data was set up fairly
unusually so that a pivot table wouldn't work. I modified the spreadsheet
and tried setting up a new pivot table, but I'm now having problems grouping
the data. The date I'm working with is in a mm/dd/yy format, and it will
group the payments by single dates, but I'm not sure how to use the pivot
table to display for an entire month now. Thanks.

"Sabine" wrote:

Have you tried using a pivot table. You can very easily group by year and
month.

"unofarmbob" wrote:

I'm working with a spreadsheet that keeps track of checks written to
customers, and need to be able to total the payments for each month. The
spreadsheet begins in Nov, 04, and will continue to grow into the future, so
I'm trying to create a total for each month of each year. I've created a
function to check the date because there are text values within the data that
give errors when I try and run the MONTH and YEAR functions in Excel 2003.
So, I created this function:

Function MonthTotal(MonthNeeded, YearNeeded, VisitDate, Amount)

If VisitDate = "Last Visit Date" Then
MonthTotal = 0
Else
If Month(VisitDate) = MonthNeeded Then
If Year(VisitDate) = YearNeeded Then
MonthTotal = Amount
Else: MonthTotal = 0
End If
Else: MonthTotal = 0
End If
End If

End Function

The function works to check a single data point, but I'm having trouble
using it now to sum up the totals for the month. I thought about trying to
create another funtion that runs a loop through all the data that then calls
this function and keeps a running total, but I'm not very familiar with VB
and don't know the process. Any help as to what I should do would be great.
Thanks a lot!

you can group by month by right-clicking on a date label, group, select
'month'.

also can you insert a column in the data with a formula like
=text([date cell],"mmmm-yy") to get a new month label to group by.
this can be useful if you have financial years that don't start on 1
Jan and need to manipulate the month number.

regards..


--
steven1001
------------------------------------------------------------------------
steven1001's Profile: http://www.excelforum.com/member.php...o&userid=30757
View this thread: http://www.excelforum.com/showthread...hreadid=548616