Count and Sum Functions based on multiple column criteria

[Mike]

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?

 

[smartin]

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.