Count unique cells based on column of blank cells

[monkeyharris]

Hi,

I've managed to get part of my formula to work which counts the number of unique records in column B. I only need it to run a calculation on those cells in G which are blank. Any ideas? Formula is in L5727 as i was testing it there.

2024 Woodford Proactive Calls.xls
	A	B	C	D	E	F	G	H	I	J	K	L
5726	15/03/24	1CS22555	WO	Alina Maria	Sales-i	Yes	Alina	3	45382
5727	15/03/24	1CS22555	WO	Alina Maria	Sales-i	Yes	Alina	3	45382			910
5728	15/03/24	1CS22569	WO	Alina Maria	Sales-i	Yes	Alina	3	45382
5729	15/03/24	1CS22569	WO	Alina Maria	Sales-i	Yes	Alina	3	45382
5730	15/03/24	1CS22581	WO	Alina Maria	Sales-i	Yes	Alina	3	45382
5731	15/03/24	1CS22581	WO	Alina Maria	Sales-i	Yes	Alina	3	45382
5732	15/03/24	1CS22590	WO	Alina Maria	Sales-i	Yes	Alina	3	45382
5733	15/03/24	1CS22590	WO	Alina Maria	Sales-i	Yes	Alina	3	45382
5734	15/03/24	1CS22592	WO	Alina Maria	Sales-i	Yes	Alina	3	45382
5735	15/03/24	1CS22592	WO	Alina Maria	Sales-i	Yes	Alina	3	45382
5736	15/03/24	1CS22596	WO	Alina Maria	Sales-i	Yes	Alina	3	45382
5737	15/03/24	1CS22596	WO	Alina Maria	Sales-i	Yes	Alina	3	45382
5738	15/03/24	1CS22607	WO	Alina Maria	Sales-i	Yes		3	45382
5739	15/03/24	1CS22607	WO	Alina Maria	Sales-i	Yes		3	45382
5740	15/03/24	1CS22613	WO	Alina Maria	Sales-i	Yes		3	45382
5741	15/03/24	1CS22613	WO	Alina Maria	Sales-i	Yes		3	45382
5742	15/03/24	1CS22624	WO	Alina Maria	Sales-i	Yes		3	45382
5743	15/03/24	1CS22624	WO	Alina Maria	Sales-i	Yes		3	45382
5744	15/03/24	1CS22630	WO	Alina Maria	Sales-i	Yes		3	45382
5745	15/03/24	1CS22630	WO	Alina Maria	Sales-i	Yes		3	45382
YTD_Figures

Cell Formulas
Range	Formula
H5726:H5745	H5726	=IF(I5726=45322,1,IF(I5726=45351,2,IF(I5726=45382,3,0)))
I5726:I5745	I5726	=EOMONTH(A5726,0)
L5727	L5727	=SUM(IF(FREQUENCY(IF(LEN(B2:B5789)>0,MATCH(B2:B5789,B2:B5789,0),""),IF(LEN(B2:B5789)>0,MATCH(B2:B5789,B2:B5789,0),""))>0,1))
G5726:G5737	G5726	=VLOOKUP(B5726,WO_Bronze!$B$2:$J$947,9,FALSE)

 

[Peter_SSs]

If I am understanding correctly, try this instead of the formula you currently have in L5727

=LET(r,B2:B5789,ROWS(UNIQUE(FILTER(r,r<>""))))

For the added condition, try

=LET(r,B2:B5789,IFNA(ROWS(UNIQUE(FILTER(r,(r<>"")*(G2:G5789=""),NA()))),0))

 

[monkeyharris]

If I am understanding correctly, try this instead of the formula you currently have in L5727

=LET(r,B2:B5789,ROWS(UNIQUE(FILTER(r,r<>""))))

For the added condition, try

=LET(r,B2:B5789,IFNA(ROWS(UNIQUE(FILTER(r,(r<>"")*(G2:G5789=""),NA()))),0))

Peter, that's amazing and thank you so much for your wisdom. Works perfectly.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

 

[monkeyharris]

You're welcome. Thanks for the follow-up.

Hi Peter,
Sorry, me again. Column E will only ever contain 3 names "Sales Order", "Sales Quote" and "Sales-i". Is there a way to use the formula and adapt it so it looks how many transactions there are in column B (your formula: =LET(r,B2:B5789,ROWS(UNIQUE(FILTER(r,r<>"")))) and count how many say "Sales Order", "Sales Quote" or "Sales-i".
Or possibly the number of unique customers (your formula: =LET(r,B2:B5789,ROWS(UNIQUE(FILTER(r,r<>"")))) and the same number of Sales Order", "Sales Quote" or "Sales-i". .

Here's what the sheet looks like now with the table being populated:

15/03/24	1CS22555	WO	Alina Maria	Sales-i	Yes	Alina	3	45382
15/03/24	1CS22555	WO	Alina Maria	Sales-i	Yes	Alina	3	45382
15/03/24	1CS22569	WO	Alina Maria	Sales-i	Yes	Alina	3	45382
15/03/24	1CS22569	WO	Alina Maria	Sales-i	Yes	Alina	3	45382
15/03/24	1CS22581	WO	Alina Maria	Sales-i	Yes	Alina	3	45382			GOLD - SILVER ACCOUNTS CONTACTED
15/03/24	1CS22581	WO	Alina Maria	Sales-i	Yes	Alina	3	45382			Transactions	Q1 Customers	Sales Generated	Quotes Issued	Contact Only
15/03/24	1CS22590	WO	Alina Maria	Sales-i	Yes	Alina	3	45382			910	494
15/03/24	1CS22590	WO	Alina Maria	Sales-i	Yes	Alina	3	45382
15/03/24	1CS22592	WO	Alina Maria	Sales-i	Yes	Alina	3	45382			Transactions	Total number of Gold & Silver Sales Orders, Quotes or Sales-i entries.
15/03/24	1CS22592	WO	Alina Maria	Sales-i	Yes	Alina	3	45382			Q1 Customers	Total number of Gold & Silver accounts with entries
15/03/24	1CS22596	WO	Alina Maria	Sales-i	Yes	Alina	3	45382			Sales Orders	Total number of Gold & Silver Sales Orders
15/03/24	1CS22596	WO	Alina Maria	Sales-i	Yes	Alina	3	45382			Quotes Issued	Total number of Gold & Silver Quotes issued
15/03/24	1CS22607	WO	Alina Maria	Sales-i	Yes		3	45382			Contact Only	Total number of Gold & Silver Entries in Sales-i
15/03/24	1CS22607	WO	Alina Maria	Sales-i	Yes		3	45382
15/03/24	1CS22613	WO	Alina Maria	Sales-i	Yes		3	45382
15/03/24	1CS22613	WO	Alina Maria	Sales-i	Yes		3	45382
15/03/24	1CS22624	WO	Alina Maria	Sales-i	Yes		3	45382
15/03/24	1CS22624	WO	Alina Maria	Sales-i	Yes		3	45382
15/03/24	1CS22630	WO	Alina Maria	Sales-i	Yes		3	45382
15/03/24	1CS22630	WO	Alina Maria	Sales-i	Yes		3	45382

 

[Peter_SSs]

I'm not understanding. Could you make up a small set of dummy data (say 20-30 rows), enter the results for that small sample data manually, post it all (including headings & results table) with XL2BB and explain again in relation to that sample data and results? Part of that explanation might include what "Gold & Silver" has to do with anything as I have no idea.

Also, when using XL2BB please use Mini Sheet not Table Only so we can see row numbers, column headings etc.

 

[monkeyharris]

I'm not understanding. Could you make up a small set of dummy data (say 20-30 rows), enter the results for that small sample data manually, post it all (including headings & results table) with XL2BB and explain again in relation to that sample data and results? Part of that explanation might include what "Gold & Silver" has to do with anything as I have no idea.

Also, when using XL2BB please use Mini Sheet not Table Only so we can see row numbers, column headings etc.

Morning Peter,
Sorry, i should have made it clearer. I've included a table showing how the date should look and also where the data comes from. What i'm trying to achieve is something to do what you've already sent me (if cells in column G are blank, calculate how many unique account numbers relate to those cells in column B).
Now what i'm looking for is the same but only give results based on the 3 choices in column E which will only ever be "Sales Order", "Sales Quote" and Sales-i".

You'd probably change all my formulas but i've built it with what limited knowledge i have.

2024 Woodford Proactive Calls-1.xls
	I	J	K	L	M
1	GOLD - SILVER ACCOUNTS CONTACTED
2	Transactions	Q1 Customers	Sales Generated	Quotes Issued	Contact Only
3	4535	494	2940	982	613
Statistics

Cell Formulas
Range	Formula
J3	J3	=LET(r,YTD_Figures!B2:B5789,IFNA(ROWS(UNIQUE(FILTER(r,(r<>"")*(YTD_Figures!G2:G5789=""),NA()))),0))

2024 Woodford Proactive Calls-1.xls
	A	B	C	D	E	F	G	H	I
1	Call Date	Account Number	Owning Branch Code	Sales Rep Code	Method	Contacted	User	Month
2	02/01/24	1TAP1174	WO	Nick Barker	Sales Order	Yes		1	45322
3	02/01/24	1TAP1083	WO	Nick Barker	Sales Order	Yes		1	45322
4	02/01/24	1TAP1126	WO	Nick Barker	Sales Order	Yes		1	45322
5	02/01/24	1TAP1001	WO	Nick Barker	Sales Order	Yes		1	45322
6	02/01/24	1TAP1001	WO	Nick Barker	Sales Order	Yes		1	45322
8	02/01/24	1BUI019	WO	Nick Butcher	Sales Order	Yes		1	45322
9	02/01/24	1CAS043R	WO	Woodford Trade Ctr	Sales Order	Yes		1	45322
10	02/01/24	1BUI019	WO	Nick Butcher	Sales Order	Yes		1	45322
11	02/01/24	1CAR154	WO	Nick Barker	Sales Order	Yes		1	45322
12	02/01/24	1CAS043R	WO	Woodford Trade Ctr	Sales Order	Yes		1	45322
13	02/01/24	1BUI019	WO	Nick Butcher	Sales Order	Yes		1	45322
15	02/01/24	1BUI019	WO	Nick Butcher	Sales Order	Yes		1	45322
16	02/01/24	1ONE009	WO	Nick Butcher	Sales Order	Yes		1	45322
17	02/01/24	1ECJ001	WO	Jo Amos SE	Sales Order	Yes		1	45322
18	02/01/24	1WYC004	WO	Nick Butcher	Sales Order	Yes		1	45322
19	02/01/24	1YES001	WO	Nick Butcher	Sales Order	Yes		1	45322
20	02/01/24	1CAR154	WO	Nick Barker	Sales Order	Yes		1	45322
21	02/01/24	1CAR154	WO	Nick Barker	Sales Order	Yes		1	45322
22	02/01/24	1CAS043R	WO	Woodford Trade Ctr	Sales Order	Yes		1	45322
23	02/01/24	1TOP619	WO	Steve Warsap	Sales Order	Yes		1	45322
24	02/01/24	1ONE009	WO	Nick Butcher	Sales Order	Yes		1	45322
25	02/01/24	1CAR154	WO	Nick Barker	Sales Order	Yes		1	45322
26	02/01/24	1BUI019	WO	Nick Butcher	Sales Order	Yes		1	45322
27	02/01/24	1BAL003	WO	Jo Amos TK	Sales Order	Yes		1	45322
28	02/01/24	1CAS043R	WO	Woodford Trade Ctr	Sales Order	Yes		1	45322
29	02/01/24	1BUI019	WO	Nick Butcher	Sales Order	Yes		1	45322
30	02/01/24	1BUI019	WO	Nick Butcher	Sales Order	Yes		1	45322
31	02/01/24	1COS004	WO	Nick Barker	Sales Order	Yes		1	45322
32	02/01/24	1COS004	WO	Nick Barker	Sales Order	Yes		1	45322
33	02/01/24	1COS004	WO	Nick Barker	Sales Order	Yes		1	45322
34	02/01/24	1CS12821	WO	Nick Butcher	Sales Order	Yes		1	45322
35	02/01/24	1CS28273	WO	Alina Maria	Sales Order	Yes		1	45322
36	02/01/24	1COS004	WO	Nick Barker	Sales Order	Yes		1	45322
37	02/01/24	1COS004	WO	Nick Barker	Sales Order	Yes		1	45322
38	02/01/24	1COS004	WO	Nick Barker	Sales Order	Yes		1	45322
39	02/01/24	1COS004	WO	Nick Barker	Sales Order	Yes		1	45322
40	02/01/24	1COS004	WO	Nick Barker	Sales Order	Yes		1	45322
41	02/01/24	1COS004	WO	Nick Barker	Sales Order	Yes		1	45322
42	02/01/24	1COS004	WO	Nick Barker	Sales Order	Yes		1	45322
43	02/01/24	1COS004	WO	Nick Barker	Sales Order	Yes		1	45322
45	02/01/24	1COS004	WO	Nick Barker	Sales Order	Yes		1	45322
46	02/01/24	1CS2922	WO	Nick Butcher	Sales Order	Yes		1	45322
47	02/01/24	1COS004	WO	Nick Barker	Sales Order	Yes		1	45322
48	02/01/24	1COS004	WO	Nick Barker	Sales Order	Yes		1	45322
49	02/01/24	1CS11288	WO	Nick Butcher	Sales Order	Yes		1	45322
50	02/01/24	1COS004	WO	Nick Barker	Sales Order	Yes		1	45322
51	02/01/24	1COS004	WO	Nick Barker	Sales Order	Yes		1	45322
52	02/01/24	1COS004	WO	Nick Barker	Sales Order	Yes		1	45322
53	02/01/24	1COS004	WO	Nick Barker	Sales Order	Yes		1	45322
54	02/01/24	1MOD010	WO	Jo Amos TK	Sales Order	Yes		1	45322
55	02/01/24	1COS004	WO	Nick Barker	Sales Order	Yes		1	45322
56	02/01/24	1ONE009	WO	Nick Butcher	Sales Order	Yes		1	45322
57	02/01/24	1CAS043R	WO	Woodford Trade Ctr	Sales Order	Yes		1	45322
58	02/01/24	1CAS043R	WO	Woodford Trade Ctr	Sales Order	Yes		1	45322
59	02/01/24	1COS004	WO	Nick Barker	Sales Order	Yes		1	45322
60	02/01/24	1COS004	WO	Nick Barker	Sales Order	Yes		1	45322
61	02/01/24	1BUI019	WO	Nick Butcher	Sales Order	Yes		1	45322
62	02/01/24	1COS004	WO	Nick Barker	Sales Order	Yes		1	45322
63	02/01/24	1CAS043R	WO	Woodford Trade Ctr	Sales Order	Yes		1	45322
64	02/01/24	1CAS043R	WO	Woodford Trade Ctr	Sales Order	Yes		1	45322
65	02/01/24	1CAS043T	WO	Woodford Trade Ctr	Sales Order	Yes		1	45322
66	02/01/24	1ONE009	WO	Nick Butcher	Sales Order	Yes		1	45322
67	02/01/24	1IFI001	WO	Nick Butcher	Sales Order	Yes		1	45322
68	02/01/24	1WRE005	WO	Nick Butcher	Sales Order	Yes		1	45322
69	02/01/24	1CS8183	WO	Jo Amos SE	Sales Order	Yes		1	45322
70	02/01/24	1CS12821	WO	Nick Butcher	Sales Order	Yes		1	45322
71	02/01/24	1CAS043R	WO	Woodford Trade Ctr	Sales Order	Yes		1	45322
73	02/01/24	1YES001	WO	Nick Butcher	Sales Order	Yes		1	45322
74	02/01/24	1LLK001	WO	Jamie Batt	Sales Order	Yes		1	45322
75	02/01/24	1CS2922	WO	Nick Butcher	Sales Order	Yes		1	45322
76	02/01/24	1GEN024	WO	Jo Amos TK	Sales Order	Yes		1	45322
78	02/01/24	1JBL003	WO	Jo Amos SE	Sales Order	Yes		1	45322
79	02/01/24	1JAC020	WO	Nick Butcher	Sales Order	Yes		1	45322
80	02/01/24	1BAT104	WO	Nick Butcher	Sales Order	Yes		1	45322
81	02/01/24	1LLK001	WO	Jamie Batt	Sales Order	Yes		1	45322
82	02/01/24	1LLK001	WO	Jamie Batt	Sales Order	Yes		1	45322
83	02/01/24	1JAC020	WO	Nick Butcher	Sales Order	Yes		1	45322
84	02/01/24	1CS11163	WO	Alan Beynon	Sales Order	Yes		1	45322
86	02/01/24	3JEW562	WO	Nick Butcher	Sales Quote	Yes		1	45322
87	02/01/24	1GRI004	WO	Jo Amos SE	Sales Quote	Yes		1	45322
88	02/01/24	1CAS043R	WO	Woodford Trade Ctr	Sales Quote	Yes		1	45322
89	02/01/24	1CAS043R	WO	Woodford Trade Ctr	Sales Quote	Yes		1	45322
91	02/01/24	1CAS043R	WO	Woodford Trade Ctr	Sales Quote	Yes		1	45322
92	02/01/24	1CAS043R	WO	Woodford Trade Ctr	Sales Quote	Yes		1	45322
93	02/01/24	1CAS043R	WO	Woodford Trade Ctr	Sales Quote	Yes		1	45322
94	02/01/24	1CAS043R	WO	Woodford Trade Ctr	Sales Quote	Yes		1	45322
95	02/01/24	1CAS043R	WO	Woodford Trade Ctr	Sales Quote	Yes		1	45322
96	02/01/24	1CAS043R	WO	Woodford Trade Ctr	Sales Quote	Yes		1	45322
97	02/01/24	1CAS043R	WO	Woodford Trade Ctr	Sales Quote	Yes		1	45322
98	02/01/24	1CAS043R	WO	Woodford Trade Ctr	Sales Quote	Yes		1	45322
100	02/01/24	1CAS043R	WO	Woodford Trade Ctr	Sales Quote	Yes		1	45322
101	02/01/24	1WRE005	WO	Nick Butcher	Sales Quote	Yes		1	45322
102	02/01/24	1CAS043R	WO	Woodford Trade Ctr	Sales Quote	Yes		1	45322
103	02/01/24	1CAS043R	WO	Woodford Trade Ctr	Sales Quote	Yes		1	45322
104	02/01/24	1HUW242	WO	Alan Beynon	Sales Quote	Yes		1	45322
107	02/01/24	1CAS043R	WO	Woodford Trade Ctr	Sales Quote	Yes		1	45322
110	02/01/24	1CAS043R	WO	Woodford Trade Ctr	Sales Quote	Yes		1	45322
111	02/01/24	1CAS043R	WO	Woodford Trade Ctr	Sales Quote	Yes		1	45322
112	02/01/24	1CAS043R	WO	Woodford Trade Ctr	Sales Quote	Yes		1	45322
114	02/01/24	1CAS043R	WO	Woodford Trade Ctr	Sales Quote	Yes		1	45322
115	02/01/24	1CAS043	WO	Woodford Trade Ctr	Sales Quote	Yes		1	45322
116	02/01/24	1CAS043T	WO	Woodford Trade Ctr	Sales Quote	Yes		1	45322
117	02/01/24	1CS11163	WO	Alan Beynon	Sales Quote	Yes		1	45322
118	02/01/24	1CS27862	WO	Paul Hollingshead	Sales Quote	Yes		1	45322
119	02/01/24	1CS22678	WO	Paul Hollingshead	Sales Quote	Yes		1	45322
120	02/01/24	1CS22678	WO	Paul Hollingshead	Sales Quote	Yes		1	45322
121	02/01/24	1CS22678	WO	Paul Hollingshead	Sales Quote	Yes		1	45322
122	02/01/24	1CS22678	WO	Paul Hollingshead	Sales Quote	Yes		1	45322
123	03/01/24	1TAP1051	WO	Nick Barker	Sales Order	Yes		1	45322
124	03/01/24	1TAP1049	WO	Nick Barker	Sales Order	Yes		1	45322
125	03/01/24	1TAP1201	WO	Nick Barker	Sales Order	Yes		1	45322
126	03/01/24	1TAP1133	WO	Nick Barker	Sales Order	Yes		1	45322
127	03/01/24	1TAP1051	WO	Nick Barker	Sales Order	Yes		1	45322
128	03/01/24	1ROO013	WO	Nick Butcher	Sales Order	Yes		1	45322
129	03/01/24	1ROO013	WO	Nick Butcher	Sales Order	Yes		1	45322
130	03/01/24	1CAR154	WO	Nick Barker	Sales Order	Yes		1	45322
132	03/01/24	1COS004	WO	Nick Barker	Sales Order	Yes		1	45322
134	03/01/24	1CAS043R	WO	Woodford Trade Ctr	Sales Order	Yes		1	45322
135	03/01/24	1COS004	WO	Nick Barker	Sales Order	Yes		1	45322
136	03/01/24	1CAS043R	WO	Woodford Trade Ctr	Sales Order	Yes		1	45322
137	03/01/24	1CAR154	WO	Nick Barker	Sales Order	Yes		1	45322
138	03/01/24	1MOD010	WO	Jo Amos TK	Sales Order	Yes		1	45322
139	03/01/24	1BUI019	WO	Nick Butcher	Sales Order	Yes		1	45322
140	03/01/24	1CS12821	WO	Nick Butcher	Sales Order	Yes		1	45322
141	03/01/24	1COS004	WO	Nick Barker	Sales Order	Yes		1	45322
142	03/01/24	1COS004	WO	Nick Barker	Sales Order	Yes		1	45322
143	03/01/24	1BUI019	WO	Nick Butcher	Sales Order	Yes		1	45322
144	03/01/24	1COS004	WO	Nick Barker	Sales Order	Yes		1	45322
145	03/01/24	1CAS043R	WO	Woodford Trade Ctr	Sales Order	Yes		1	45322
146	03/01/24	1BAL003	WO	Jo Amos TK	Sales Order	Yes		1	45322
147	03/01/24	1COS004	WO	Nick Barker	Sales Order	Yes		1	45322
148	03/01/24	1PRS002	WO	Alan Beynon	Sales Order	Yes		1	45322
149	03/01/24	1HUW242	WO	Alan Beynon	Sales Order	Yes		1	45322
151	03/01/24	1BSF001	WO	Jo Amos TK	Sales Order	Yes		1	45322
152	03/01/24	1GAM005	WO	Nick Butcher	Sales Order	Yes		1	45322
154	03/01/24	1MBS003	WO	Nick Butcher	Sales Order	Yes		1	45322
155	03/01/24	1FIT018	WO	Jo Amos TK	Sales Order	Yes		1	45322
156	03/01/24	1BUI019	WO	Nick Butcher	Sales Order	Yes		1	45322
157	03/01/24	1WAS002	WO	Jo Amos TK	Sales Order	Yes		1	45322
158	03/01/24	1BUR031	WO	Jo Amos SE	Sales Order	Yes		1	45322
160	03/01/24	1WAR037	WO	Nick Butcher	Sales Order	Yes		1	45322
161	03/01/24	1CAS043R	WO	Woodford Trade Ctr	Sales Order	Yes		1	45322
162	03/01/24	1WAS002	WO	Jo Amos TK	Sales Order	Yes		1	45322
163	03/01/24	1CS11288	WO	Nick Butcher	Sales Order	Yes		1	45322
164	03/01/24	1COS004	WO	Nick Barker	Sales Order	Yes		1	45322
165	03/01/24	1COS004	WO	Nick Barker	Sales Order	Yes		1	45322
166	03/01/24	1COS004	WO	Nick Barker	Sales Order	Yes		1	45322
167	03/01/24	1COS004	WO	Nick Barker	Sales Order	Yes		1	45322
168	03/01/24	1CS11288	WO	Nick Butcher	Sales Order	Yes		1	45322
169	03/01/24	1ONW001	WO	Jo Amos SE	Sales Order	Yes		1	45322
170	03/01/24	1ECH003	WO	Jo Amos SE	Sales Order	Yes		1	45322
171	03/01/24	1CAS043R	WO	Woodford Trade Ctr	Sales Order	Yes		1	45322
173	03/01/24	1CAS043R	WO	Woodford Trade Ctr	Sales Order	Yes		1	45322
174	03/01/24	1CAS043R	WO	Woodford Trade Ctr	Sales Order	Yes		1	45322
177	03/01/24	1CS15408	WO	Alan Beynon	Sales Order	Yes		1	45322
178	03/01/24	1MOD010	WO	Jo Amos TK	Sales Order	Yes		1	45322
180	03/01/24	1CAS043R	WO	Woodford Trade Ctr	Sales Order	Yes		1	45322
181	03/01/24	1CON120	WO	Jo Amos SE	Sales Order	Yes		1	45322
182	03/01/24	1BUI019	WO	Nick Butcher	Sales Order	Yes		1	45322
184	03/01/24	1BUI019	WO	Nick Butcher	Sales Order	Yes		1	45322
185	03/01/24	1CLA106	WO	Nick Butcher	Sales Order	Yes		1	45322
186	03/01/24	1CS22678	WO	Paul Hollingshead	Sales Order	Yes		1	45322
188	03/01/24	3JEW562	WO	Nick Butcher	Sales Quote	Yes		1	45322
190	03/01/24	1FIT018	WO	Jo Amos TK	Sales Quote	Yes		1	45322
191	03/01/24	1ONE009	WO	Nick Butcher	Sales Quote	Yes		1	45322
192	03/01/24	1FIT030	WO	Jo Amos TK	Sales Quote	Yes		1	45322
193	03/01/24	1PRS002	WO	Alan Beynon	Sales Quote	Yes		1	45322
194	03/01/24	1CAS043T	WO	Woodford Trade Ctr	Sales Quote	Yes		1	45322
195	03/01/24	1MBS003	WO	Nick Butcher	Sales Quote	Yes		1	45322
196	03/01/24	1BUI019	WO	Nick Butcher	Sales Quote	Yes		1	45322
198	03/01/24	1CS28162	WO	Alina Maria	Sales Quote	Yes		1	45322
199	03/01/24	1ECH003	WO	Jo Amos SE	Sales Quote	Yes		1	45322
200	03/01/24	1CAS043R	WO	Woodford Trade Ctr	Sales Quote	Yes		1	45322
201	03/01/24	1CAS043R	WO	Woodford Trade Ctr	Sales Quote	Yes		1	45322
202	03/01/24	1CAS043R	WO	Woodford Trade Ctr	Sales Quote	Yes		1	45322
205	03/01/24	1CAS043R	WO	Woodford Trade Ctr	Sales Quote	Yes		1	45322
206	03/01/24	1CAS043R	WO	Woodford Trade Ctr	Sales Quote	Yes		1	45322
208	03/01/24	1SAN032	WO	Alan Beynon	Sales Quote	Yes		1	45322
209	03/01/24	1CAS043R	WO	Woodford Trade Ctr	Sales Quote	Yes		1	45322
210	03/01/24	1BUR031	WO	Jo Amos SE	Sales-i	Yes		1	45322
211	03/01/24	1CS28301	WO	Alina Maria	Sales-i	Yes		1	45322
212	03/01/24	1BUR031	WO	Jo Amos SE	Sales-i	Yes		1	45322
213	03/01/24	1GRI004	WO	Jo Amos SE	Sales-i	Yes		1	45322
217	04/01/24	1TAP1122	WO	Nick Barker	Sales Order	Yes		1	45322
218	04/01/24	1TAP1083	WO	Nick Barker	Sales Order	Yes		1	45322
219	04/01/24	1TAP1122	WO	Nick Barker	Sales Order	Yes		1	45322
221	04/01/24	1ONE009	WO	Nick Butcher	Sales Order	Yes		1	45322
222	04/01/24	1BEN3844	WO	Alan Beynon	Sales Order	Yes		1	45322
YTD_Figures

Cell Formulas
Range	Formula
H2:H6,H8:H13,H15:H43,H45:H71,H73:H76,H78:H84,H86:H89,H91:H98,H100:H104,H107,H110:H112,H114:H130,H132,H134:H149,H151:H152,H154:H158,H160:H171,H173:H174,H177:H178,H180:H182,H184:H186,H188,H190:H196,H198:H202,H205:H206,H208:H213,H217:H219,H221:H222	H2	=IF(I2=45322,1,IF(I2=45351,2,IF(I2=45382,3,0)))
I2:I6,I8:I13,I15:I43,I45:I71,I73:I76,I78:I84,I86:I89,I91:I98,I100:I104,I107,I110:I112,I114:I130,I132,I134:I149,I151:I152,I154:I158,I160:I171,I173:I174,I177:I178,I180:I182,I184:I186,I188,I190:I196,I198:I202,I205:I206,I208:I213,I217:I219,I221:I222	I2	=EOMONTH(A2,0)

 

[monkeyharris]

I'm not understanding. Could you make up a small set of dummy data (say 20-30 rows), enter the results for that small sample data manually, post it all (including headings & results table) with XL2BB and explain again in relation to that sample data and results? Part of that explanation might include what "Gold & Silver" has to do with anything as I have no idea.

Also, when using XL2BB please use Mini Sheet not Table Only so we can see row numbers, column headings etc.

If you want the workbook let me know and i'll email.

 

[Peter_SSs]

If you want the workbook let me know and i'll email.

That is not permitted - see #4 of the Forum Rules regarding taking questions off-forum.

Could you make up a small set of dummy data (say 20-30 rows), ....

Your sample is hardly 20-30 rows and the results shown do not seem to correspond to the data that is shown. For example Q1 customers in your sheet shows 494 whereas for the sample data it appears to be <100.

Could you make up a small set of dummy data (say 20-30 rows), enter the results for that small sample data manually, post it all (including headings & results table) with XL2BB and explain again in relation to that sample data and results?

 

[Peter_SSs]

Please do not mark a post that doesn't contain a solution. I have removed the solution mark from post #9 as it certainly doesn't contain a solution to your question.