Number of vehicles

[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?

 

[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

 

[Number of Vehicles(re-suggest)]

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

 

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

---

 

[ryguy7272]

A Pivot Table seems like your best bet. If you want an alternative, try
sumproduct. Here is one example:
=SUMPRODUCT(--(C2:C9="Ford"))

There is a great tutorial on the sumproduct function here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Here are some good tutorials on working with Pivot Tables:
http://peltiertech.com/Excel/Pivots/pivottables.htm
http://www.contextures.com/xlPivot02.html


Regards,
Ryan--

 

[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

 

[Max]

2 options offered, via pivot table and using formula (sumproduct)
Illustrated in this sample:
http://www.freefilehosting.net/download/3gbcg
sumproduct n pivot options.xls

---

 

[Kula]

Thank you very, very much. It worked exactly the way I needed.

Cheers.

 

[Max]

Thank you very, very much. It worked exactly the way I needed.

Welcome. Take a moment to press the "Yes" button below.

---