Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count and Sum Functions based on multiple column criteria
I need to count and sum data in a spreadsheet based on criteria in three
separate columns. Specifically, it's dealing with discrepancy information for aircraft maintenance where one column (A) contains identifiers for individual work centers, another column (B) contains text codes for the types of discrepancies and the third column (C) contains the time spent on a repair. What I need to do is count how many of a particular type of repair in column B occurs for each work center in column A. Additionally, I need to sum the amount of time in column C for each of the count functions. The wrinkle is, that in some cases, more than one discrepancy code in column B need to be counted collectively (i.e. PX, PL and PF all would be in a single count) and their respective hour sums would need to be calculated. For example purposes, the 3 workcenters in column A are FHM, FCS and FC9. FHM had 12 "Phase" discrepancies between 1 "PX", 2 "PL" and 9 "PF" entries in column B, with their respective hours in column C. How do I construct a formula to extract that data? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count and Sum Functions based on multiple column criteria
Mike wrote:
I need to count and sum data in a spreadsheet based on criteria in three separate columns. Specifically, it's dealing with discrepancy information for aircraft maintenance where one column (A) contains identifiers for individual work centers, another column (B) contains text codes for the types of discrepancies and the third column (C) contains the time spent on a repair. What I need to do is count how many of a particular type of repair in column B occurs for each work center in column A. Additionally, I need to sum the amount of time in column C for each of the count functions. The wrinkle is, that in some cases, more than one discrepancy code in column B need to be counted collectively (i.e. PX, PL and PF all would be in a single count) and their respective hour sums would need to be calculated. For example purposes, the 3 workcenters in column A are FHM, FCS and FC9. FHM had 12 "Phase" discrepancies between 1 "PX", 2 "PL" and 9 "PF" entries in column B, with their respective hours in column C. How do I construct a formula to extract that data? A pivot table can do all this, even the grouping. No formulas required. Select your range of data, fire up the pivot table wiz, and accept all the defaults. Drag Workcenter to the row area, discrepancy to the column (or row) area, and time to the data area. To group the discrepancy field use Ctrl+Click to select the categories to group (click the field labels), then Data | Group and Outline | Group. You can then right click "Group 1" and opt to hide the detail if desired. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count based on multiple criteria | Excel Worksheet Functions | |||
Count based on multiple criteria | Excel Worksheet Functions | |||
Count based on multiple criteria | Excel Discussion (Misc queries) | |||
Count based on multiple criteria | Excel Worksheet Functions | |||
How do you create COUNTIF functions based on multiple criteria? | Excel Worksheet Functions |