Pivot Tables and "COUNT" Function

[ttocsmi]

Pardon me if this question has been asked before. I've done some searching and have yet to find an answer.

I created a pivot table from my source data, with 2 Row Label fields (subsystem and failure mode), and one Value field (part). It looks like this:

subsystem / failure mode / Count
brake / mode_1 / 1
brake / mode_2 / 6
brake / mode_3 / 4
cooling / mode_4 / 3
cooling / mode_5 / 1

How can I create a pivot table which summarizes my data by subsystem and the count of UNIQUE failure modes? Like this:

subsystem / Count
brake / 3
cooling / 2

I know that I can create a second pivot table and use as its data source the first pivot table, but that requires me to manually adjust the second pivot table's range whenever new data is added. And it's not very elegant (it's ugly).

I'd like to just create one pivot table to do this, but I don't know how.

In Access, I'd use a SELECT DISTINCT query, but that function doesn't appear in Excel.

Any idea? Thanks.

 

[Fazza]

Hi,

Distinct counts are usually a pain in Excel pivot tables. If it suits, it might be just as easy to use the SELECT DISTINCT.

regards, Fazza


for example

1. (not strictly necessary but easier to explain) give the source data a defined name [CTRL-F3 & say MyData]
2. save the file
3. from new file, ALT-D-P for new pivot table
4. take the 'external data source' at the first step, then Get Data
5. choose Excel files as your data source & follow wizard to end then choose option to edit in MS Query
6. within MS Query hit 'SQL' button and edit SQL to suit. Such as "SELECT DISTINCT subsystem, `failure mode` FROM MyData"
7. OK to enter it, see results set, hit 'open door' icon & complete pivot table
8. if you like, resultant worksheet containing pivot table can be moved into the source data file

 

[AlphaFrog]

In your Pivot Table layout, put the Subsystem in the Row area and put the Count of Subsystem in the Data area.

Excel Workbook
	F	G
9	Count of subsystem
10	subsystem	Total
11	brake	3
12	cooling	2
13	Grand Total	5
PIVOT TABLE

 

[ttocsmi]

I'm new here - how can I easily post pics from my spreadsheet? Do i have to upload to tinypic first?

 

[Fazza]

There are some sticky threads at the top of the forum with info on posting.

 

[ttocsmi]

Ok, try #2. Perhaps this is more clear:

 

[Fazza]

Yes, it is clearer. It is what I understood originally.

What I posted originally works still; just change the SQL to suit the different field names. That is
SELECT DISTINCT system, subsystem FROM YourTableName

This is a specific solution. If it is not good for you, a different specific solution can be used. OK?

 

[AlphaFrog]

Here's another way to do it if you're willing to make a "Uniques" helper column.

Excel Workbook
	A	B	C	D	E	F	G
1	System	Subsystem	Component	Uniques		Sum of Uniques
2	breaks	mechanical	pads	1		System	Total
3	breaks	mechanical	caliper	0		breaks	3
4	breaks	hydraulic	regulator	1		cooling	4
5	breaks	hydraulic	tubing	0
6	breaks	hydraulic	filter	0
7	breaks	electrical	controller	1
8	cooling	hoses	big hose	1
9	cooling	hoses	small hose	0
10	cooling	fan	fan motor	1
11	cooling	controls	knob	1
12	cooling	ducting	duct	1
...

Cell Formulas
Range	Formula
D2	=(SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=B2))=1)*1
D3	=(SUMPRODUCT(($A$2:$A3=A3)*($B$2:$B3=B3))=1)*1
D4	=(SUMPRODUCT(($A$2:$A4=A4)*($B$2:$B4=B4))=1)*1
D5	=(SUMPRODUCT(($A$2:$A5=A5)*($B$2:$B5=B5))=1)*1
D6	=(SUMPRODUCT(($A$2:$A6=A6)*($B$2:$B6=B6))=1)*1
D7	=(SUMPRODUCT(($A$2:$A7=A7)*($B$2:$B7=B7))=1)*1
D8	=(SUMPRODUCT(($A$2:$A8=A8)*($B$2:$B8=B8))=1)*1
D9	=(SUMPRODUCT(($A$2:$A9=A9)*($B$2:$B9=B9))=1)*1
D10	=(SUMPRODUCT(($A$2:$A10=A10)*($B$2:$B10=B10))=1)*1
D11	=(SUMPRODUCT(($A$2:$A11=A11)*($B$2:$B11=B11))=1)*1
D12	=(SUMPRODUCT(($A$2:$A12=A12)*($B$2:$B12=B12))=1)*1

You could use the COUNTIFS function instead of SUMPRODUCT if you have Excel 2007 or later

D2
=(COUNTIFS($A$2:$A2, A2, $B$2:$B2, B2)=1)*1

 

[ttocsmi]

Thanks Fazza. I hadn't thought of that approach. I'll give it a try.

 

[ttocsmi]

AlphaFrog - Thanks for the solution, it worked. I'm not sure how yet, but it did.