Columns to Matrix in XL2003 2

[MeGustaXL]

Hi

I want to produce a Criticality Matrix for a FMEA in XL2003. Three columns:

[b]Failure OCC	SEV[/b]
Mode2	6	4
Mode25	6	1
Mode8	10	5
Mode1	1	6
Mode26	10	10
Mode19	2	7
etc....

And I want to produce a 10 by 10 matrix with OCC along the bottom, 1 to 10 and SEV on the y axis, 1 to 10. Each intersection counts the number of OCC/SEV combinations, like this:

10				1	1	1	1			
9		1								
8				1		2		1		
7								2		
6		1								
5								1		
4	1	1				1	2	1	1	
3			1	1	1			1	1	
2										
1	1							1		
	1	2	3	4	5	6	7	8	9	10

I've done it with a Pivot Table, which worked well, but had a couple of drawbacks.
1. It wasn't dynamic enough. Each time the data in the columns changes, I have to refresh the PT.
2. It's not complete. If there are no data in any value of OCC or SEV, that column or row is omitted from the table. For example, in the table above, there are no SEV "2" values, so the PT wouldn't show that row. It is essential that all 10 rows and columns are included, whether or not they are in the data.

I've got a feeling that this can be done with OFFSETs and COUNTIFs, but I don't have the skills to put it together.

Can anyone help?



Chris

Someday I'll know what I'm donig...damn!

 

[SkipVought]

Hi,

Set up the 10 x 10, 1-10 in col A and 1-10 in row 1.

Then use SUMPRODUCT() starting in B2

=SUMPRODUCT(--($A2=OCC)*(B$1=SEV))

Using Named Ranges.

Copy down & across.

 

[MeGustaXL]

Thank you Skip

It's been a while since I've needed help, and there you were, almost instantly, and on a weekend too!

Simple and to the point as always - brilliant!

Chris

Someday I'll know what I'm donig...damn!