i want data in this format 1to30,31to183,184to335,335to365 days

[sleek12]

am using office 365 , focus is on column B,C and I

mrexcelquestion.xlsx
	A	B	C	D	E	F	G	H	I	J
332	D4648268	Type D - Motor Cycle	Opalescent Insurance Agency	BELDINAACHIENG	03/11/2023	KMGF343N	06/11/2023	05/10/2024	335.00	Active
333	D4648269	Type D - Motor Cycle	Opalescent Insurance Agency	BERYLODHIAMBO	03/11/2023	KMGD842U	############	02/11/2024	365.40	Active
334	D4648270	Type D - Motor Cycle	Opalescent Insurance Agency	GEOFFREYMWANGI	03/11/2023	KMGF933C	############	02/12/2023	29.25	Active
335	D4925956	Type D - PSV	Opalescent Insurance Agency	BELDINAACHIENG	03/11/2023	KMGK546D	############	02/11/2024	365.37	Active
336	C28415854	Type C - Private Car	PANTHERA INSURANCE AGENCY	PerfectInsurance	03/11/2023 08:38	KCD821R	05/11/2023	04/10/2024	335.00	Active
337	B13282074	Type B - Commercial Vehicle	Nelkan Insurance Agency	JanetObiero	03/11/2023	KAW071G	############	09/08/2024	280.45	Active
338	C28415855	Type C - Private Car	PANTHERA INSURANCE AGENCY	PerfectInsurance	03/11/2023 14:58	KBH847Z	04/11/2023	03/12/2023	30.00	Active
339	C28463000	Type C - Private Car	Lilian Nyambura	MainaNdee	03/11/2023 17:11	KCL441K	############	04/05/2024	183.29	Active
340	B13291497	Type B - Commercial Vehicle	Lilian Nyambura	MainaNdee	03/11/2023 17:24	KCS362A	############	02/12/2023	29.28	Active
341	C28463001	Type C - Private Car	Lilian Nyambura	MainaNdee	03/11/2023 17:29	KCN449W	############	02/12/2023	29.27	Active
usedata

to look like this, i checked i havent got GROUPBY or PIVOTBY yet on rollout

mrexcelquestion.xlsx
	A	B	C	D	E	F
3	agency	type of cover	no of days
4			1 to 30	31to183	183to335	335to365
5	Azadon Insurance Agency	Type B - Commercial Vehicle
6		Type C - Private Car
7		Type D - Motor Cycle
8		Type D - PSV
9	GRALEY INSURANCE AGENCY	Type B - Commercial Vehicle
10		Type C - Private Car
11		Type D - Motor Cycle
12		Type D - PSV
13	Jawwab Insurance Agencies	Type B - Commercial Vehicle
14		Type C - Private Car
15	Lilian Nyambura	Type B - Commercial Vehicle
16		Type C - Private Car
17		Type D - Motor Cycle
18		Type D - PSV
19	MERKEL BLUE INSURANCE AGENCY	Type B - Commercial Vehicle
20		Type C - Private Car
21		Type D - PSV
22	Nelkan Insurance Agency	Type B - Commercial Vehicle
23		Type C - Private Car
24		Type D - Motor Cycle
25		Type D - PSV
26	NOVASURE INSURANCE AGENCY	Type B - Commercial Vehicle
27		Type C - Private Car
28		Type D - Motor Cycle
29		Type D - PSV
30	Opalescent Insurance Agency	Type B - Commercial Vehicle
31		Type C - Private Car
32		Type D - Motor Cycle
33		Type D - PSV
34	PANTHERA INSURANCE AGENCY	Type B - Commercial Vehicle
35		Type C - Private Car
36		Type D - PSV
37	REMEMBER INSURANCE AGENCY	Type B - Commercial Vehicle
38		Type C - Private Car
39		Type D - Motor Cycle
40		Type D - PSV
41	Royal Mat Insurance Agency	Type B - Commercial Vehicle
42		Type C - Private Car
43		Type D - Motor Cycle
44		Type D - PSV
45	TRUWYSE INSURANCE AGENCY	Type B - Commercial Vehicle
46		Type C - Private Car
47		Type D - Motor Cycle
48		Type D - PSV
49	Urbanlink Insurance Agency	Type B - Commercial Vehicle
50		Type C - Private Car
51		Type D - Motor Cycle
52		Type D - PSV
Sheet4

 

[Eric W]

Maybe:

Book1
	A	B	C	D	E	F
1	agency	type of cover	no of days
2			1 to 30	31 to 183	184 to 335	336 to 365
3	Lilian Nyambura	Type B - Commercial Vehicle	0	0	0	0
4		Type C - Private Car	0	0	0	0
5	Nelkan Insurance Agency	Type B - Commercial Vehicle	280.4451	0	0	0
6	Opalescent Insurance Agency	Type D - Motor Cycle	0	700.4007	0	0
7		Type D - PSV	0	365.3653	0	0
8	PANTHERA INSURANCE AGENCY	Type C - Private Car	0	335	0	0
9
Sheet4

Cell Formulas
Range	Formula
A3:B8	A3	=LET(d,CHOOSE({1,2},usedata!C:C,usedata!B:B),f,UNIQUE(FILTER(d,(usedata!C:C<>"")*(usedata!C:C<>"agency"))),SORT(f,{1,2},{1,1}))
C3:F8	C3	=SUMIFS(usedata!I:I,usedata!C:C,INDEX(A3#,0,1),usedata!B:B,INDEX(A3#,0,2),usedata!H:H,">"&TODAY()+{0,30,183,335},usedata!H:H,"<="&TODAY()+{30,183,335,365})
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A28	Expression	=A2=A1	text	NO

Note that in column A, the agency names are actually in the blank cells, I just used Conditional Formatting to show them as blank. Also, the totals in the C:F columns are a bit of a guess. I assumed you wanted the totals matching the A:B columns, and distributed to the C:F columns based on how far out the date is in column H of the table. Let me know if this is close to what you want.

 

[sleek12]

I want no of counts in the cells, those appear to be no of days !!

 

[Eric W]

The numbers in the cells are the sum of the amounts. If you just want the counts:

Book1
	A	B	C	D	E	F
1	agency	type of cover	no of days
2			1 to 30	31 to 183	184 to 335	336 to 365
3	Lilian Nyambura	Type B - Commercial Vehicle	0	0	0	0
4		Type C - Private Car	0	0	0	0
5	Nelkan Insurance Agency	Type B - Commercial Vehicle	1	0	0	0
6	Opalescent Insurance Agency	Type D - Motor Cycle	0	2	0	0
7		Type D - PSV	0	1	0	0
8	PANTHERA INSURANCE AGENCY	Type C - Private Car	0	1	0	0
Sheet4

Cell Formulas
Range	Formula
A3:B8	A3	=LET(d,CHOOSE({1,2},usedata!C:C,usedata!B:B),f,UNIQUE(FILTER(d,(usedata!C:C<>"")*(usedata!C:C<>"agency"))),SORT(f,{1,2},{1,1}))
C3:F8	C3	=COUNTIFS(usedata!C:C,INDEX(A3#,0,1),usedata!B:B,INDEX(A3#,0,2),usedata!H:H,">"&TODAY()+{0,30,183,335},usedata!H:H,"<="&TODAY()+{30,183,335,365})
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A28	Expression	=A2=A1	text	NO

 

[sleek12]

Not from today , i checked it out now, there are riots in country as we speak.
i want it sans TODAY() , only focus on no. of days .
Its not an ageing list !! column I data should be static.
I removed TODAY() and i got zeroes

 

[Eric W]

Do you mean the number of days between the G and H columns? Try:

Book1
	A	B	C	D	E	F
1	agency	type of cover	no of days
2			1 to 30	31 to 183	184 to 335	336 to 365
3	Lilian Nyambura	Type B - Commercial Vehicle	1	0	0	0
4		Type C - Private Car	1	1	0	0
5	Nelkan Insurance Agency	Type B - Commercial Vehicle	0	0	1	0
6	Opalescent Insurance Agency	Type D - Motor Cycle	1	0	1	1
7		Type D - PSV	0	0	0	1
8	PANTHERA INSURANCE AGENCY	Type C - Private Car	1	0	1	0
Sheet4

Cell Formulas
Range	Formula
A3:B8	A3	=LET(d,CHOOSE({1,2},usedata!C:C,usedata!B:B),f,UNIQUE(FILTER(d,(usedata!C:C<>"")*(usedata!C:C<>"agency"))),SORT(f,{1,2},{1,1}))
C3:F8	C3	=SUM((usedata!$C$2:$C$20=$A3)*(usedata!$B$2:$B$20=$B3)*(MATCH(usedata!$H$2:$H$20-usedata!$G$2:$G$20,{0,31,184,336,366})=COLUMNS($C3:C3)))
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A28	Expression	=A2=A1	text	NO

I wasn't able to come up with a SPILL formula for this. I also am lacking some of the newer functions that might allow that.

 

[sleek12]

thanks,
i made a slight modification to your formula to accommodate my real worksheet (at workplace)
=SUM((usedata!$C$2:$C$26339=$A3)*(usedata!$B$2:$B$26339=$B3)*(MATCH(usedata!$H$2:$H$26339-usedata!$G$2:$G$26339,{0,31,184,336,366})=COLUMNS($C3:C3)))
but the LET function i let it stay as it is, i will validate it tomorrow !! i hope am not going wrong, the one with TODAY() function will come in helpful.

 

[sleek12]

i have a small challenge here, i analysed it and the count was less than 26339 rows. if it can be circumvented. what i dont get is how the count was shown inspite of rule <> "agency"

i suppose it could be this formula here =LET(d,CHOOSE({1,2},usedata!C:C,usedata!B:B),f,UNIQUE(FILTER(d,(usedata!C:C<>"")*(usedata!C:C<>"agency"))),SORT(f,{1,2},{1,1}))

mrexcelquestion.xlsx
	A	B	C	D	E	F
11	Jawwab Insurance Agencies	Type B - Commercial Vehicle	0	0	20	0
12	Jawwab Insurance Agencies	Type C - Private Car	0	11	64	0
13	Lilian Nyambura	Type B - Commercial Vehicle	3	207	184	0
14	Lilian Nyambura	Type C - Private Car	4	508	318	0
Sheet1

 

[Eric W]

I don't understand what your challenge is?

I included the (usedata!C:C<>"agency") part of the formula so that I could use the whole column references. "agency" is the header in column C, so that section was just intended to exclude the header from the results. If you use the actual range of the data, like you did in post 7, you can eliminate that part, like this:

=LET(d,CHOOSE({1,2},usedata!C2:C26339,usedata!B2:B26339),f,UNIQUE(FILTER(d,usedata!C2:C26339<>"")),SORT(f,{1,2},{1,1}))

In fact, if you use the actual range, and there are no blank rows in the range, you can eliminate the whole FILTER function (and it's now simple enough to not need LET):

=SORT(UNIQUE(CHOOSE({1,2},usedata!C2:C26339,usedata!B2:B26339)),{1,2},{1,1})

 

[sleek12]

The challenge was only I couldn't discern how to get the rest which didn't have agency written. Anyway I shall try those out.
Thank you for your assistance, am much obliged.