Hello. I'm setting up a results summary table that calculates from a detail
tab.
In the detail tab i'm capturing date of activity (where the user inputs a
date) 3/31/08. On the summary tab i want to calculate results by month year.
I inserted a column next to the activity date column in the detail tab;
tried and failed at formatting the m/dd/yy format to month year (Mar-2008) to
allow me to count and group by month year. Suggestions?
--
Thank you -- Suzanne.

=SUMPRODUCT(--(YEAR(A2:A200)=2008),--(MONTH(A2:A200)=3))

as an example for March

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Suzanne" wrote in message
...
Hello. I'm setting up a results summary table that calculates from a
detail
tab.
In the detail tab i'm capturing date of activity (where the user inputs a
date) 3/31/08. On the summary tab i want to calculate results by month
year.
I inserted a column next to the activity date column in the detail tab;
tried and failed at formatting the m/dd/yy format to month year (Mar-2008)
to
allow me to count and group by month year. Suggestions?
--
Thank you -- Suzanne.

Insert a helper column and create a column of dates using the following formula

=DATE(YEAR(A1),MONTH(A1),1)

Substituting the A1 in the expample with your first date cell. Copy the
formula down to the last row and then format the formula date column to
display month/year.

Now that you have monthly dates with the same day you grouping by month year
will now work as planned.

Hope this helps.
--
Kevin Backmann


"Suzanne" wrote:

Hello. I'm setting up a results summary table that calculates from a detail
tab.
In the detail tab i'm capturing date of activity (where the user inputs a
date) 3/31/08. On the summary tab i want to calculate results by month year.
I inserted a column next to the activity date column in the detail tab;
tried and failed at formatting the m/dd/yy format to month year (Mar-2008) to
allow me to count and group by month year. Suggestions?
--
Thank you -- Suzanne.

EasyFilter have a option to do this.
You can use the subtotal function if you want if you filter on the same sheet
http://www.rondebruin.nl/easyfilter.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Suzanne" wrote in message ...
Hello. I'm setting up a results summary table that calculates from a detail
tab.
In the detail tab i'm capturing date of activity (where the user inputs a
date) 3/31/08. On the summary tab i want to calculate results by month year.
I inserted a column next to the activity date column in the detail tab;
tried and failed at formatting the m/dd/yy format to month year (Mar-2008) to
allow me to count and group by month year. Suggestions?
--
Thank you -- Suzanne.

Thanks to all. Kevin's formuals was exactly what i was looking for. Many
thanks!
--
Thank you -- Suzanne.


"Kevin B" wrote:

Insert a helper column and create a column of dates using the following formula

=DATE(YEAR(A1),MONTH(A1),1)

Substituting the A1 in the expample with your first date cell. Copy the
formula down to the last row and then format the formula date column to
display month/year.

Now that you have monthly dates with the same day you grouping by month year
will now work as planned.

Hope this helps.
--
Kevin Backmann


"Suzanne" wrote:

Hello. I'm setting up a results summary table that calculates from a detail
tab.
In the detail tab i'm capturing date of activity (where the user inputs a
date) 3/31/08. On the summary tab i want to calculate results by month year.
I inserted a column next to the activity date column in the detail tab;
tried and failed at formatting the m/dd/yy format to month year (Mar-2008) to
allow me to count and group by month year. Suggestions?
--
Thank you -- Suzanne.