Dynamic critera for database functions

[Greg Allen]

I have a table of data similar to the following:

Name A B C D E F
Rich 4 4 3 3 4 4
Dave 4 3 3 4 4 4
Rich 4 4 4 4 4 4
John 4 4 4 4 4 4
Rich 4 4 4 3 3 3
Rich 3 3 3 2 3 3
Dave 4 3 3 3 4 4

I would like to use the database functions like DCOUNTA, DSUM, etc. to work
with this data, grouping it by name summing all the columns. So the end
result on my other worksheet would look like this:

Name A B C D E F
Rich 15 14 14 13 14 14
Dave 8 6 6 7 8 8
John 4 4 4 4 4 4

Note that the Name column on the summed worksheet doesn't have to
be generated. I put the list of names there by hand. However, I want all the
formulas in the other columns on the sheet to match up that Name column
with the Name column on the data.

While I'm only showing summing of the data here, there are more columns
in my actual data where I'm doing averages, etc.

I have this working using SUMPRODUCT and array formulas, but it's really
slow
so I am looking at a way to speed it up. I've thought of pivot tables, but
have
never used them and would prefer not to learn right now unless I am forced
to.

I think there has got to be a way to do this using the database functions
and the criteria. But I haven't been able to figure it out.

Can anyone help?

Thanks,

-- Greg

 

[Dave Peterson]

It sounds like a perfect time to learn about pivottables.

If you've never used pivottables, here are a few links:

Debra Dalgleish's pictures at Jon Peltier's site:
http://peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistance/2002/articles/xlconPT101.aspx

 

[Greg Allen]

OK, I've got a pivot table set up and it is fairly easy.

However, I'm left with one remaining problem. Take my data
in the pivot table:

Name A B C D E F
Rich 15 14 14 13 14 14
Dave 8 6 6 7 8 8
John 4 4 4 4 4 4

I would like to take an average of columns B:F and put it on the right hand
side
of the table (note that I am excluding column A). And I'd like to be able
to
sort on the column.

Can I create a "custom" field in a pivot table and do this? I can create
exactly
what I want by just using a formula (outside of the pivot table) in the
column
to the right of the table. But that doesn't allow me to sort on it.

I haven't been able to find a way to do this yet. Any suggestions?

Thanks!

 

[Dave Peterson]

Maybe you can use a custom calculation.

Debra Dalgleish explains how:
http://contextures.com/xlPivot10.html