converting input table to output table using formulas

[SRana]

Hi,

i have an input table which i want to convert to the output table using Dynamic Formulas/Functions.. Request help on this..

Input Table: (X1..X9 represent column headers)

X1	X2	X3	X4	X5	X6	X7	X8	X9
NQ	Selected L1	NQ	L2	NQ	L3	NQ	NQ	NQ
L5	L2	NQ	Selected L1	L4	L6	L3	NQ	L7
L3	NQ	L2	L4	NQ	L5	Selected L1	NQ	NQ
L3	NQ	Selected L1	L2	NQ	L4	NQ	L5	NQ
L2	L7	Selected L1	L3	NQ	NQ	L4	L5	L6
L2	NQ	Selected L1	L3	NQ	NQ	NQ	NQ	NQ
L4	L3	NQ	L2	L5	Selected L1	NQ	NQ	NQ
Selected L1	NQ	NQ	L3	NQ	NQ	NQ	NQ	L2
L4	NQ	L2	Selected L1	L3	NQ	NQ	NQ	NQ
L2	NQ	L5	Selected L1	L4	L6	NQ	L3	NQ
L2	NQ	NQ	Selected L1	NQ	NQ	L3	NQ	L4
L2	NQ	NQ	L3	NQ	Selected L1	NQ	NQ	NQ

<colgroup><col><col span="3"><col><col span="2"><col><col></colgroup><tbody>
</tbody>

Output Table: Output table throws out results for count against each vendor for every field

Vendor	Selected L1	L2	L3	L4	L5	L6	L7	NQ
X1	1	5	2	2	1			1
X2	1	1	1				1	8
X3	3	2			1			6
X4	4	3	4	1
X5			1	2	1			8
X6	2		1	1	1	2		5
X7	1		2	1				8
X8			1		2			9
X9		1		1		1	1	8

<colgroup><col><col><col span="6"><col></colgroup><tbody>
</tbody>

 

[DRSteele]

Welcome to the forum.

As you may have noticed, COUNTIFS chokes on arrays. This will do it though. Copy the B16 formula across and down.

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]

Book1
	A	B	C	D	E	F	G	H	I
1	X1	X2	X3	X4	X5	X6	X7	X8	X9
2	NQ	Selected L1	NQ	L2	NQ	L3	NQ	NQ	NQ
3	L5	L2	NQ	Selected L1	L4	L6	L3	NQ	L7
4	L3	NQ	L2	L4	NQ	L5	Selected L1	NQ	NQ
5	L3	NQ	Selected L1	L2	NQ	L4	NQ	L5	NQ
6	L2	L7	Selected L1	L3	NQ	NQ	L4	L5	L6
7	L2	NQ	Selected L1	L3	NQ	NQ	NQ	NQ	NQ
8	L4	L3	NQ	L2	L5	Selected L1	NQ	NQ	NQ
9	Selected L1	NQ	NQ	L3	NQ	NQ	NQ	NQ	L2
10	L4	NQ	L2	Selected L1	L3	NQ	NQ	NQ	NQ
11	L2	NQ	L5	Selected L1	L4	L6	NQ	L3	NQ
12	L2	NQ	NQ	Selected L1	NQ	NQ	L3	NQ	L4
13	L2	NQ	NQ	L3	NQ	Selected L1	NQ	NQ	NQ
14
15	Vendor	Selected L1	L2	L3	L4	L5	L6	L7	NQ
16	X1	1	5	2	2	1	0	0	1
17	X2	1	1	1	0	0	0	1	8
18	X3	3	2	0	0	1	0	0	6
19	X4	4	3	4	1	0	0	0	0
20	X5	0	0	1	2	1	0	0	8
21	X6	2	0	1	1	1	2	0	5
22	X7	1	0	2	1	0	0	0	8
23	X8	0	0	1	0	2	0	0	9
24	X9	0	1	0	1	0	1	1	8
Sheet54

Cell Formulas
Range	Formula
B16	=SUMPRODUCT(($A16=$A$1:$I$1)*($A$2:$I$13=B$15))

[/FONT]

 

[SRana]

Wow!! Thanks very much DRSteele .. saved much time..

 

[DRSteele]

That's super. You're welcome.