I am looking for a function that will group data in a table similar to the
the way this works in Access queries. For example, if I have a table with
one column defined as date, and another column is the amout of money spent,
I would like to be able to put a formula at the bottom of the columns that
will sum the money collumn for each month.

I am familiar with the capability of pivot tables - they work great, but
pivot tables are a bit of a pain to create, and unkeep, etc. I am familiar
with sumif and array formulas to perform this task. Both slow the workbook
to a crawl if you are trying to analyse large datasets.

I am also curious if anyone has a user defined function that if given a
date, will tell you how many days are in the month? For example,
Function(2/13/2004 ) = 29. I can think of a way to do this, but it seems
like I am making the problem more complicated than it is. I suspect that
someone knows an extremely easy way - maybe there is already a funcion in
excel to do this?

Thank you,
Chris

You described the solutions and discounted them all.

If you think sumif is slow, then there probably is no solution for you.
Maybe you want to try DSUM.

=DAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,0))

gives you the number of days in the current month.

--
Regards,
Tom Ogilvy





"C. Bailey" wrote in message
news:8E0fc.300$zv6.8@clgrps12...
I am looking for a function that will group data in a table similar to the
the way this works in Access queries. For example, if I have a table with
one column defined as date, and another column is the amout of money
spent,
I would like to be able to put a formula at the bottom of the columns that
will sum the money collumn for each month.

I am familiar with the capability of pivot tables - they work great, but
pivot tables are a bit of a pain to create, and unkeep, etc. I am
familiar
with sumif and array formulas to perform this task. Both slow the
workbook
to a crawl if you are trying to analyse large datasets.

I am also curious if anyone has a user defined function that if given a
date, will tell you how many days are in the month? For example,
Function(2/13/2004 ) = 29. I can think of a way to do this, but it seems
like I am making the problem more complicated than it is. I suspect that
someone knows an extremely easy way - maybe there is already a funcion in
excel to do this?

Thank you,
Chris

You can query a (closed) workbook using SQL via the MS OLEDB provdider
for Jet, therefore you *can* use a GROUP BY clause on Excel data in
exactly the same way you'd use it in MS Access. The main choices for
SQL data access in Excel are MS Query (GUI tools) and ADO (VBA code).

--

"C. Bailey" wrote in message news:<8E0fc.300$zv6.8@clgrps12...
I am looking for a function that will group data in a table similar to the
the way this works in Access queries. For example, if I have a table with
one column defined as date, and another column is the amout of money spent,
I would like to be able to put a formula at the bottom of the columns that
will sum the money collumn for each month.

I am familiar with the capability of pivot tables - they work great, but
pivot tables are a bit of a pain to create, and unkeep, etc. I am familiar
with sumif and array formulas to perform this task. Both slow the workbook
to a crawl if you are trying to analyse large datasets.

I am also curious if anyone has a user defined function that if given a
date, will tell you how many days are in the month? For example,
Function(2/13/2004 ) = 29. I can think of a way to do this, but it seems
like I am making the problem more complicated than it is. I suspect that
someone knows an extremely easy way - maybe there is already a funcion in
excel to do this?

Thank you,
Chris

That should be less of a pain to create and upkeep than pivot tables. <g

--
Regards,
Tom Ogilvy


"onedaywhen" wrote in message
om...
You can query a (closed) workbook using SQL via the MS OLEDB provdider
for Jet, therefore you *can* use a GROUP BY clause on Excel data in
exactly the same way you'd use it in MS Access. The main choices for
SQL data access in Excel are MS Query (GUI tools) and ADO (VBA code).

--

"C. Bailey" wrote in message
news:<8E0fc.300$zv6.8@clgrps12...
I am looking for a function that will group data in a table similar to
the
the way this works in Access queries. For example, if I have a table
with
one column defined as date, and another column is the amout of money
spent,
I would like to be able to put a formula at the bottom of the columns
that
will sum the money collumn for each month.

I am familiar with the capability of pivot tables - they work great, but
pivot tables are a bit of a pain to create, and unkeep, etc. I am
familiar
with sumif and array formulas to perform this task. Both slow the
workbook
to a crawl if you are trying to analyse large datasets.

I am also curious if anyone has a user defined function that if given a
date, will tell you how many days are in the month? For example,
Function(2/13/2004 ) = 29. I can think of a way to do this, but it
seems
like I am making the problem more complicated than it is. I suspect
that
someone knows an extremely easy way - maybe there is already a funcion
in
excel to do this?

Thank you,
Chris

Sometimes you have to threaten to give the client what they asked for
before they see it isn't what they want <g.

--

"Tom Ogilvy" wrote in message ...
That should be less of a pain to create and upkeep than pivot tables. <g

--
Regards,
Tom Ogilvy


"onedaywhen" wrote in message
om...

You can query a (closed) workbook using SQL via the MS OLEDB provdider
for Jet, therefore you *can* use a GROUP BY clause on Excel data in
exactly the same way you'd use it in MS Access.