TALLY TABLE using VLOOKUP & COUNTIF

[dudematters21]

Hello Masters, may I ask for your help with my current project. Just cant get the right vlookup and countif formula for them..
So I'm trying to automate this table with 2 categories - color and status. Basing on the report given below the table.
Hope someone could help. TIA.

 

[DanteAmor]

Try:

DANTE AMOR
	A	B	C	D	E	F	G	H	I
1		new	old	return			Report Chart
2	red	3	1	0			Item	Color	Status
3	orange	0	1	0			1	red	new
4	green	1	0	1			2	red	new
5	yellow	2	0	1			3	yellow	new
6							4	green	return
7							5	red	new
8							6	orange	old
9							7	yellow	new
10							8	yellow	return
11							9	green	new
12							10	red	old
Hoja2

Cell Formulas
Range	Formula
B2:D5	B2	=COUNTIFS($H:$H,$A2,$I:$I,B$1)

 

[dudematters21]

OH NICE THIS WORKS FINE, WAS GETTING CRAZY MIXING VLOOKUP AND COUNTIF.. THANKS THANKS

Try:

DANTE AMOR
	A	B	C	D	E	F	G	H	I
1		new	old	return			Report Chart
2	red	3	1	0			Item	Color	Status
3	orange	0	1	0			1	red	new
4	green	1	0	1			2	red	new
5	yellow	2	0	1			3	yellow	new
6							4	green	return
7							5	red	new
8							6	orange	old
9							7	yellow	new
10							8	yellow	return
11							9	green	new
12							10	red	old
Hoja2

Cell Formulas
Range	Formula
B2:D5	B2	=COUNTIFS($H:$H,$A2,$I:$I,B$1)

 

[Cubist]

You can get a spill array by grabbing all the headers.

Book3
	A	B	C	D	E	F	G	H	I
1		new	old	return			Report Chart
2	red	3	1	0			Item	Color	Status
3	orange	0	1	0			1	red	new
4	green	1	0	1			2	red	new
5	yellow	2	0	1			3	yellow	new
6							4	green	return
7							5	red	new
8							6	orange	old
9							7	yellow	new
10							8	yellow	return
11							9	green	new
12							10	red	old
Sheet2

Cell Formulas
Range	Formula
B2:D5	B2	=COUNTIFS(H:H,A2:A5,I:I,B1:D1)
Dynamic array formulas.