I want to create a function or formula to determine the number of vehicles of
type Pickup, Sedan, Truck, Van and SUV assigned to each department IT,
Maintenance, Purchasing, Planning, Admin and Academic. Below is the worksheet
called Vehicles to work from and followed by the worksheet to work in to
determine the vehichle type assigned to each department. Please help, very
urgent....



A B C D E
F G H
(1) VEHICLE DATA
(2)
(3) ID Year Make Type Odometer Department
(4) EB122 2003 Nissan Van 24575 Academic
(5) EM911 2004 Toyota Truck 98702 Academic
(6) CW001 1995 Ford SUV 32765 Administration
(7) CY723 1995 Ford Pickup 73419
Administration
(8) CD234 1997 Hyundai Hilux 32564 Medical
(9) FD321 1993 Hino Sedan 94321 Planning
(10) CN210 1991 Toyota Van 138456 IT
(11) DT592 2002 Ford Sedan 37780 Maintenance


Below is the worksheet called Summary to be used to determine the number of
vehicle type assigned to each department by using the above worksheet.

A B C D E
F G
(1)
(2) Type IT Maintenance Purchasing Planning
Admin Academic
(3) Pickup
(4) Sedan
(5) Truck
(6) Van
(7) SUV

"Type" and "Department" are defined name ranges

Summary sheet:
B3: =SUMPRODUCT(--(Type=$A3),--(Department=B$2))

copy across and down


"Kula" wrote:

I want to create a function or formula to determine the number of vehicles of
type Pickup, Sedan, Truck, Van and SUV assigned to each department IT,
Maintenance, Purchasing, Planning, Admin and Academic. Below is the worksheet
called Vehicles to work from and followed by the worksheet to work in to
determine the vehichle type assigned to each department. Please help, very
urgent....



A B C D E
F G H
(1) VEHICLE DATA
(2)
(3) ID Year Make Type Odometer Department
(4) EB122 2003 Nissan Van 24575 Academic
(5) EM911 2004 Toyota Truck 98702 Academic
(6) CW001 1995 Ford SUV 32765 Administration
(7) CY723 1995 Ford Pickup 73419
Administration
(8) CD234 1997 Hyundai Hilux 32564 Medical
(9) FD321 1993 Hino Sedan 94321 Planning
(10) CN210 1991 Toyota Van 138456 IT
(11) DT592 2002 Ford Sedan 37780 Maintenance


Below is the worksheet called Summary to be used to determine the number of
vehicle type assigned to each department by using the above worksheet.

A B C D E
F G
(1)
(2) Type IT Maintenance Purchasing Planning
Admin Academic
(3) Pickup
(4) Sedan
(5) Truck
(6) Van
(7) SUV