Differentiating data to create pivot table

[jonybandana]

Hello. So I have the following table:

I am posting an image because formatting is important.

I would like to generate a pivot table that looks like this:

WIRES

JAN

FEB

MAR

APR

MAY

JUN

JUL

AUG

SEP

OCT

NOV

DEC

1029​

1​

1022​

1​

1​

1034​

1​

Basically, this table should tell me how many 1029 wires I need to use (considering they only need to be used when the cell is green). Is there a way I can generate this using a pivot table? If I use a count table like this:

Row Labels	Sum of MAR	Sum of APR	Sum of MAY	Sum of JUN	Count of JUL
1022		1​	2​	1​
1029	1​
1034		1​	1​
Grand Total	1​	2​	3​	1​

Because it is also counting the instances in which it is blue, and I should use tires instead of wires.

Is there any way in which I can tell the pivot table to only count blue colored cells? Or do I have to differentiate the values in the table for excel to be able to differentiate them?

 

[awoohaw]

What is the rule for making the cell fill color green? If that is a CF rule, or a consistent rule you manually apply EVERY time, then that can be of use.

 

[jonybandana]

What is the rule for making the cell fill color green? If that is a CF rule, or a consistent rule you manually apply EVERY time, then that can be of use.

It is a manual rule, there is no condition for it. If it helps, one could be created, since green always follows an empy cell and green always follows a number value (from left to right).

 

[awoohaw]

Well, that is consistent. But, what if there are 3 or more consecutive numbers?
So you mean blue follows a number value?

So, if there are two consecutive numbers following a blank cell, the first is green and the second is blue.

 

[jonybandana]

Well, that is consistent. But, what if there are 3 or more consecutive numbers?

That does not happen. The numbers are fixed and don't change, and they are not like that. However, the parts (wires, tires, etc.) do change and are added constantly, so that would not be a worry.

 

[awoohaw]

Will this do what you need:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Model	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	WIRES	TIRES
2	A		1	1										1029	9811
3	A				1	1								1022	8812
4	B					1	1							1022	5901
5	C				1	1								1034	7782
6
7	CF RULES:
8		FALSE	TRUE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
9		FALSE	FALSE	FALSE	TRUE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
10		FALSE	FALSE	FALSE	FALSE	TRUE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
11		FALSE	FALSE	FALSE	TRUE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
12
13	Crosstab Report:
14	Wire Type	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
15	1022	0	0	0	1	1	0	0	0	0	0	0	0
16	1029	0	1	0	0	0	0	0	0	0	0	0	0
17	1034	0	0	0	1	0	0	0	0	0	0	0	0
Sheet1

Cell Formulas
Range	Formula
C1:M1	C1	=EDATE(B1,1)
B8:M11	B8	=AND(N(A2)=0,B2>0)
B14:M14	B14	=$B$1:$M$1
A15:A17	A15	=SORT(UNIQUE($N$2:$N$5,FALSE,FALSE),1,1)
B15:M17	B15	=SUM(($A$2:$L$5=0)*($B$2:$M$5=1)*(MONTH(B$14)=MONTH($B$1:$M$1))*($A15=$N$2:$N$5))
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:M5	Expression	=AND(N(A2)=0,B2>0)	text	NO