Tabulating a large list

[phd4212]

So here is my problem. I am supposed to create a tabulated table comprising
multiple different criteria for multiple people. Here is an abbreviated
version of what I am working with:
Bob 1 Cars January
Bob 2 Trucks January
Bob 1 Cars February
Jack 2 Cars January
Jack 2 Trucks February
Jack 1 Cars February
Bob 4 Trucks February
Jack 5 Cars January

Trying to get it into this format:

Employee Month Cars Trucks
Bob January
Jack January
Bob February
Jack February

Thanks

 

[Jim Thomlinson]

That is easy to do with a pivot table...

Select the data you want to summarize in a table (make sure the source data
has headers for each column). Select Data |Pivot Table and Pivot Chart...
When the wizard opens just select Finish. For a simple pivot like this XL
will make the correct choices on your behalf.

A new sheet will be created with a pivot table on it. Each of your column
headings will show up on a Field List. Drag the Employee Names to the left
hand column. Drag the Months also to the left hand column next to the
employees. Drag the numbers to the data area. Drage the Cars/Trucks to the
top row.

You can also apply an outo format to the table to make it all pretty like.
It will amaze and impress your friends.

 

[KC hotmail com>]

Pivot tables are the best approach to such tabulation. I'd probably put the
month first, then the employee...put them in the row area, then put Cars and
Trucks in the Data area. Just remember that pivot tables don't refresh
automatically, so as you work on your list throughout the year, you'll want
to hit the red exlamation mark to refresh your pivot table.

If you prefer a different approach, set up the table like you've shown below
(let's say Sheet2, A1 has the word "Employee" and they are listed starting in
A2, then B1 has the word "Month", etc.). Use this formula in C2 (under
"Cars")
=SUMPRODUCT((Sheet1!$A$1:$A$100=$A2)*(Sheet1!$D$1:$D$100=$B2)*(Sheet1!$C$1:$C$100=C$1)*(Sheet1!$B$1:$B$100))

Change the Sheet1!1:100 range to what you need, just leave the dollar signs
in there.

Now use your fill handle (bottom right corner of C2, your mouse becomes a
cross) to drag that formula to D2, then with C22 still highlighted use that
fill handle to drag down as far as you need.

The pivot table is much better, though, and your file will be much smaller
and faster.