Unique Count with Multiple criteria

[kiwiorn]

I am trying to count the number of cells that meet certain numerous criteria, using excel 2003.

For example, each job has a specific ref number and within that job ref number there is a number of lines of data, some of which we ignore and some of which we include. Each job will have a planning time using an old methodology (Planning A), a planning time using a new methodology (Planning B) and an actual time. There are a few other variables too such as the category of work.

I am trying to count the number of each unique ref no. within the sub category of work I am looking at (there are circa 50 sub categories of work). Each ref no. will only have 1 combination of work cat and sub category.

For example:

Ref# Cat Sub Rel PlanA PlanA Act
X001 A10 1000 Y 1.50 2.50 2.25
X001 A10 1000 Y 1.50 2.50 2.25
X001 A10 1000 N 0.00 0.00 0.00
X002 A10 2000 Y 2.00 1.50 1.70
X002 A10 2000 Y 2.00 1.50 1.70
X003 B10 1050 Y 3.00 3.50 6.90
X003 B10 1050 Y 3.00 3.50 6.90
X004 A10 1000 Y 2.00 1.50 4.00
X004 A10 1000 N 0.00 0.00 0.00
X005 C10 3000 N 0.00 0.00 0.00

I need to do a unique count for each individual work sub category where there is some relevant work done for each job (ref no.).

Cat Sub Count (if relevant)
A10 1000 2
A10 2000 1
B10 1050 1
C10 3000 0

Above there are only 4 relevant jobs in total. What is the count formula I use to achieve the unique count by subcategory above?

Regards
Richard

 

[Fazza]

You might be interested in a database type approach, using SQL and not worksheet formulas.

Such as if the file has been saved and the data is given a defined name, not dynamic. Say "MD" (short for MyData). (A defined need not be used, btw, but is how I will explain it.) Then go via menu data, import external data, new database query. Then Excel files, browse for your file, OK, then see the defined name and select a field or fields (from LHS to show on RHS, any will do, it will be superseded soon) & continue until the option to edit in MS Query. MS Query will open & you will see a representation of the table and returned dataset. Hit the 'SQL' icon & edit the SQL to like below - it is just text. OK to enter, then the 'open door' icon to return the dataset to a worksheet as a query table. Refreshable like a pivot table. Can be good for working with large amounts of data.

HTH, Fazza

SELECT Cat, Sub, COUNT(*) AS [Count]
FROM (
SELECT DISTINCT `Ref#`, Cat, Sub
FROM MD
WHERE Rel = 'Y')
GROUP BY Cat, Sub