Number of vehicles
 

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?

Number of vehicles
 

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
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Kula" wrote:
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?

Number of vehicles
 

"Max" wrote:

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
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Kula" wrote:
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?

it doesnt halp........isnt there any other way to solve such problem of inserting appropriate formulas rather than using pivot tables coz it doesnt help

Number of vehicles
 

"Number of Vehicles(re-suggest)" wrote:
it doesnt halp........isnt there any other way to solve such problem of inserting appropriate formulas rather than using pivot tables coz it doesnt help

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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Number of vehicles
 

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 he
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--


--
RyGuy


"Max" wrote:

"Number of Vehicles(re-suggest)" wrote:
it doesnt halp........isnt there any other way to solve such problem of inserting appropriate formulas rather than using pivot tables coz it doesnt help

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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Number of vehicles
 

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

"Kula" wrote:

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?

Number of vehicles
 

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Kula" wrote:
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

"Kula" wrote:

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?

Number of vehicles
 

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

Cheers.

"Max" wrote:

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Kula" wrote:
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

"Kula" wrote:

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?

Number of vehicles
 

"Kula" wrote:
Thank you very, very much. It worked exactly the way I needed.

Welcome. Take a moment to press the "Yes" button below.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---