Filling of table based on horizontal & vertical data

[hsandeep]

I have data as follows being generated in 2 columns, column A & column B as below:

Age	Premium
20	5000
56	4000
32	3000
45	2000
29	1000
35	2000
37	1000
48	5000
54	4000
78	3000
90	2000
1	1000
15	3000
33	2000
43	1000
75	5000

<COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY>
</TBODY>

In column E4:E10, premium is to generated in increasing order.
Based on Age (as 0 to 20, 0 & 20 punched in different cells), Total number of premium has to be filled as below.
For no filling, null ("") is required.

Premium	Age
	0	20	21	40	41	60	61	80	81	100
1000		1		2		1
2000				2		1				1
3000		1		1				1
4000						2
5000		1				1		1

<COLGROUP><COL style="WIDTH: 48pt" span=11 width=64><TBODY>
</TBODY>

How to accomplish?
Thanks in advance.

 

[Robert Mika]

Excel 2010

	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	RELATIONSHIP	AGE	Premium	Effective Premium
2	SELF	47	5000	5000		Effective	Ageband
3	SPOUSE	43		5000		Premium	0	20	21	40	41	60	61	80	81	100
4	SON	19		5000		1000		2		2
5	SON	17		5000		2000
6	SELF	50	4000	4000		3000
7	SPOUSE	46		4000		4000		1		1		2
8	DAUGHTER	21		4000		5000		2				3
9	DAUGHTER	15		4000
10	SELF	38	1000	1000
11	SPOUSE	31		1000
12	SON	11		1000
13	SON	7		1000
14	SON	46		5000

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula I4 =IF(COUNTIFS($E$2:$E$14,$G4,$C$2:$C$14,">="&H$3,$C$2:$C$14,"<="&$I$3)=0,"",COUNTIFS($E$2:$E$14,$G4,$C$2:$C$14,">="&H$3,$C$2:$C$14,"<="&$I$3)) K4 =IF(COUNTIFS($E$2:$E$14,$G4,$C$2:$C$14,">="&J$3,$C$2:$C$14,"<="&$K$3)=0,"",COUNTIFS($E$2:$E$14,$G4,$C$2:$C$14,">="&J$3,$C$2:$C$14,"<="&$K$3)) M4 =IF(COUNTIFS($E$2:$E$14,$G4,$C$2:$C$14,">="&L$3,$C$2:$C$14,"<="&$M$3)=0,"",COUNTIFS($E$2:$E$14,$G4,$C$2:$C$14,">="&L$3,$C$2:$C$14,"<="&$M$3)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>