Looking for function similar to Access "Group By" queries

[C. Bailey]

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

 

[Tom Ogilvy]

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.

 

[onedaywhen]

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

 

[Tom Ogilvy]

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

 

[onedaywhen]

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

--