| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 --- |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 --- |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 --- |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Formula to change scientific number to regular number or text | Excel Discussion (Misc queries) | |||
| countif formula to find the occurances of a number that is greater than one number but less than another | Excel Discussion (Misc queries) | |||
| Looking for form to track mileage of trucking company vehicles. | Excel Discussion (Misc queries) | |||
| excel format cells/Number/Category: Number problem | Excel Discussion (Misc queries) | |||
| Rounding a number to a multiple quantity that adds to a fixed total number | Excel Worksheet Functions | |||
Linear Mode
