Number of vehicles

K

Kula

This is a sample worksheet named Vehicle Data to work from.

ID YEAR MAKE TYPE ODOMETER DEPARTMENT
EB122 2003 Nissan Van 24575 Academic
EM911 2004 Toyota Truck 98702 Academic
CW001 1995 Ford SUV 32765 Administration
CY723 1995 Ford Pickup 73419 Administration
CD234 1997 Mistubish Hilux 32564 Medical
FD321 1993 Hino Sedan 94321 Planning
CN210 1991 Toyota Van 138456 IT
DT592 2002 Ford Sedan 37780 Maintenance

I want to make another worksheet to be called Summary to determine the
number of vehicles used by each department. What function am I to use?
 
M

Max

Try a pivot table, takes only seconds to set-up

Select a cell inside your data table,
click Data > Pivot Table
Click Next > Next
In step 3 of the wizard, click Layout
drag n drop Department into ROW area
drag n drop Make into DATA area
(it'll appear as Count of Make)
Click OK > Finish. That's it!

Hop over to the pivot sheet (just to the left) for the summary
 
N

Number of Vehicles(re-suggest)

Max said:
Try a pivot table, takes only seconds to set-up

Select a cell inside your data table,
click Data > Pivot Table
Click Next > Next
In step 3 of the wizard, click Layout
drag n drop Department into ROW area
drag n drop Make into DATA area
(it'll appear as Count of Make)
Click OK > Finish. That's it!

Hop over to the pivot sheet (just to the left) for the summary
 
M

Max

It doesn't help? Don't you get the results that you're looking for? Or, the
pivot approach somehow doesn't appeal to you for some reason?

In your summary sheet,
List* the departments in A2 down, eg: Academic, etc
Then place this in B2: =COUNTIF('Vehicle Data'!F:F,A2)
Copy down. The returns should suffice for your needs.

*the uniques list of departments would be an auto-generated output in the
pivot approach.
 
K

Kula

I am using MS Excell 2003. To make it more clearer this is a Worksheet called
Summary that will determine the number of vehicles assigned or used by each
Department. What formula or function do I need to determine the type of
vehicle for each department on the worksheet below using MS Excell 2003.

A B C D E
etc....
(1) Department
(2)Type IT Maintenance Purchasing Planning
(3)Pickup
(4)Sedan
(5)Truck
(6)Van
(7)SUV
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top