Excel formula to find the highest value of a named item in a range in the one year prior to today

[benejindu]

Gentlemen,
I have a table that records for each day, the sales quantities of different products. The record is updated every day. The list contains records from 2021 till today, and will continue to be updated.

I need a formula that can help me find the highest sale quantity (of each of the products) that occurred within the prior year from each day.

Kindly help.

 

[benejindu]

Thanks for that.
How about this for the max value & just use minifs for the min value

Fluff.xlsm
	A	B	C	D
1	Date	Company	Close	52 Weeks High
2	01/11/2022	FLOURMILL	30.3	30.3
3	01/11/2022	ZENITHBANK	20	20
4	02/11/2022	FLOURMILL	30.3	30.3
5	02/11/2022	ZENITHBANK	19.95	20
6	03/11/2022	FLOURMILL	30.3	30.3
7	03/11/2022	ZENITHBANK	19.9	20
8	04/11/2022	FLOURMILL	30.3	30.3
9	04/11/2022	ZENITHBANK	19.85	20
10	07/11/2022	FLOURMILL	30.3	30.3
11	07/11/2022	ZENITHBANK	20	20
12	08/11/2022	FLOURMILL	30.3	30.3
13	08/11/2022	ZENITHBANK	19.9	20
14	09/11/2022	FLOURMILL	30.3	30.3
15	09/11/2022	ZENITHBANK	19.95	20
16	10/11/2022	FLOURMILL	30.3	30.3
17	10/11/2022	ZENITHBANK	19.9	20
18	11/11/2022	FLOURMILL	27.3	30.3
19	11/11/2022	ZENITHBANK	19.95	20
20	14/11/2022	FLOURMILL	27.3	30.3
21	14/11/2022	ZENITHBANK	19.95	20
22	15/11/2022	FLOURMILL	27.3	30.3
23	15/11/2022	ZENITHBANK	20.1	20.1
24	16/11/2022	FLOURMILL	27.3	30.3
25	16/11/2022	ZENITHBANK	21.8	21.8
26	17/11/2022	FLOURMILL	27.3	30.3
27	17/11/2022	ZENITHBANK	21.3	21.8
28	18/11/2022	FLOURMILL	27.3	30.3
29	18/11/2022	ZENITHBANK	21.35	21.8
30	21/11/2022	FLOURMILL	27.3	30.3
31	21/11/2022	ZENITHBANK	21.7	21.8
32	22/11/2022	FLOURMILL	27.3	30.3
33	22/11/2022	ZENITHBANK	22.1	22.1
34	23/11/2022	FLOURMILL	27.3	30.3
35	23/11/2022	ZENITHBANK	22.7	22.7
36	24/11/2022	FLOURMILL	27.3	30.3
37	24/11/2022	ZENITHBANK	22	22.7
38	25/11/2022	FLOURMILL	28.1	30.3
39	25/11/2022	ZENITHBANK	21.9	22.7
40	28/11/2022	FLOURMILL	28.1	30.3
41	28/11/2022	ZENITHBANK	22	22.7
42	29/11/2022	FLOURMILL	28.1	30.3
43	29/11/2022	ZENITHBANK	21.7	22.7
44	30/11/2022	FLOURMILL	28.1	30.3
45	30/11/2022	ZENITHBANK	21.9	22.7
46	01/12/2022	FLOURMILL	28.1	30.3
47	01/12/2022	ZENITHBANK	22.75	22.75
48	02/12/2022	FLOURMILL	28.1	30.3
49	02/12/2022	ZENITHBANK	22.75	22.75
50	05/12/2022	FLOURMILL	28.1	30.3
51	05/12/2022	ZENITHBANK	22.2	22.75
52	06/12/2022	FLOURMILL	28.1	30.3
53	06/12/2022	ZENITHBANK	22.05	22.75
54	07/12/2022	FLOURMILL	27.6	30.3
55	07/12/2022	ZENITHBANK	22	22.75
56	08/12/2022	FLOURMILL	27.6	30.3
57	08/12/2022	ZENITHBANK	22.05	22.75
58	09/12/2022	FLOURMILL	27.6	30.3
59	09/12/2022	ZENITHBANK	22.1	22.75
60	12/12/2022	FLOURMILL	27.6	30.3
61	12/12/2022	ZENITHBANK	22.45	22.75
62	13/12/2022	FLOURMILL	27.6	30.3
63	13/12/2022	ZENITHBANK	23	23
64	14/12/2022	FLOURMILL	27.6	30.3
65	14/12/2022	ZENITHBANK	23	23
66	15/12/2022	FLOURMILL	27.6	30.3
67	15/12/2022	ZENITHBANK	23.5	23.5
68	16/12/2022	FLOURMILL	27.6	30.3
69	16/12/2022	ZENITHBANK	24.1	24.1
70	19/12/2022	FLOURMILL	27.6	30.3
71	19/12/2022	ZENITHBANK	24	24.1
72	20/12/2022	FLOURMILL	27.6	30.3
73	20/12/2022	ZENITHBANK	24.25	24.25
74	21/12/2022	FLOURMILL	27.6	30.3
75	21/12/2022	ZENITHBANK	24.3	24.3
76	22/12/2022	FLOURMILL	27.6	30.3
77	22/12/2022	ZENITHBANK	24.1	24.3
78	23/12/2022	FLOURMILL	27.6	30.3
79	23/12/2022	ZENITHBANK	24.55	24.55
80	28/12/2022	FLOURMILL	27.6	30.3
81	28/12/2022	ZENITHBANK	24.9	24.9
82	29/12/2022	FLOURMILL	28.4	30.3
83	29/12/2022	ZENITHBANK	24.9	24.9
84	30/12/2022	FLOURMILL	28.4	30.3
85	30/12/2022	ZENITHBANK	24	24.9
86	03/01/2023	FLOURMILL	28.4	30.3
87	03/01/2023	ZENITHBANK	24	24.9
88	04/01/2023	FLOURMILL	30	30.3
89	04/01/2023	ZENITHBANK	24.1	24.9
90	05/01/2023	FLOURMILL	30	30.3
91	05/01/2023	ZENITHBANK	24.55	24.9
92	06/01/2023	FLOURMILL	29.9	30.3
93	06/01/2023	ZENITHBANK	24.45	24.9
94	09/01/2023	FLOURMILL	29	30.3
95	09/01/2023	ZENITHBANK	24.3	24.9
96	10/01/2023	FLOURMILL	29	30.3
97	10/01/2023	ZENITHBANK	24.3	24.9
98	11/01/2023	FLOURMILL	29	30.3
99	11/01/2023	ZENITHBANK	24.4	24.9
100	12/01/2023	FLOURMILL	29	30.3
101	12/01/2023	ZENITHBANK	25.1	25.1
102	13/01/2023	FLOURMILL	30	30.3
103	13/01/2023	ZENITHBANK	25.55	25.55
104	16/01/2023	FLOURMILL	30	30.3
105	16/01/2023	ZENITHBANK	24.6	25.55
106	17/01/2023	FLOURMILL	30.9	30.9
107	17/01/2023	ZENITHBANK	24.6	25.55
108	18/01/2023	FLOURMILL	30.9	30.9
109	18/01/2023	ZENITHBANK	24.6	25.55
110	19/01/2023	FLOURMILL	30.9	30.9
111	19/01/2023	ZENITHBANK	24.55	25.55
112	20/01/2023	FLOURMILL	30.9	30.9
113	20/01/2023	ZENITHBANK	24.45	25.55
114	23/01/2023	FLOURMILL	30.9	30.9
115	23/01/2023	ZENITHBANK	24.45	25.55
116	24/01/2023	FLOURMILL	30.9	30.9
117	24/01/2023	ZENITHBANK	24.5	25.55
118	25/01/2023	FLOURMILL	30.9	30.9
119	25/01/2023	ZENITHBANK	24.5	25.55
120	26/01/2023	FLOURMILL	30.9	30.9
121	26/01/2023	ZENITHBANK	24.7	25.55
122	27/01/2023	FLOURMILL	30.9	30.9
123	27/01/2023	ZENITHBANK	24.7	25.55
124	30/01/2023	FLOURMILL	30.9	30.9
125	30/01/2023	ZENITHBANK	24.9	25.55
126	31/01/2023	FLOURMILL	30.9	30.9
127	31/01/2023	ZENITHBANK	25.1	25.55
128	01/02/2023	FLOURMILL	30	30.9
129	01/02/2023	ZENITHBANK	25.5	25.55
130	02/02/2023	FLOURMILL	30	30.9
131	02/02/2023	ZENITHBANK	25.5	25.55
132	03/02/2023	FLOURMILL	30	30.9
133	03/02/2023	ZENITHBANK	25.1	25.55
134	06/02/2023	FLOURMILL	29.95	30.9
135	06/02/2023	ZENITHBANK	25.1	25.55
136	07/02/2023	FLOURMILL	29.95	30.9
137	07/02/2023	ZENITHBANK	25	25.55
138	08/02/2023	FLOURMILL	29.95	30.9
139	08/02/2023	ZENITHBANK	25.2	25.55
140	09/02/2023	FLOURMILL	29.95	30.9
141	09/02/2023	ZENITHBANK	25.1	25.55
142	10/02/2023	FLOURMILL	29.95	30.9
143	10/02/2023	ZENITHBANK	25.1	25.55
144	14/02/2023	FLOURMILL	30	30.9
145	14/02/2023	ZENITHBANK	25.3	25.55
146	15/02/2023	FLOURMILL	30	30.9
147	15/02/2023	ZENITHBANK	25.35	25.55
148	16/02/2023	FLOURMILL	30	30.9
149	16/02/2023	ZENITHBANK	25.5	25.55
150	17/02/2023	FLOURMILL	30	30.9
151	17/02/2023	ZENITHBANK	25.5	25.55
152	20/02/2023	FLOURMILL	30.9	30.9
153	20/02/2023	ZENITHBANK	25	25.55
154	21/02/2023	FLOURMILL	30.9	30.9
155	21/02/2023	ZENITHBANK	25	25.55
156	22/02/2023	FLOURMILL	31	31
157	22/02/2023	ZENITHBANK	25.2	25.55
158	23/02/2023	FLOURMILL	31	31
159	23/02/2023	ZENITHBANK	25.35	25.55
160	24/02/2023	FLOURMILL	32.05	32.05
161	24/02/2023	ZENITHBANK	25.75	25.75
162	28/02/2023	FLOURMILL	32	32.05
163	28/02/2023	ZENITHBANK	26.6	26.6
164	01/03/2023	FLOURMILL	32	32.05
165	01/03/2023	ZENITHBANK	26.65	26.65
166	02/03/2023	FLOURMILL	32	32.05
167	02/03/2023	ZENITHBANK	26.4	26.65
168	03/03/2023	FLOURMILL	33.5	33.5
169	03/03/2023	ZENITHBANK	26.6	26.65
170	06/03/2023	FLOURMILL	32.95	33.5
171	06/03/2023	ZENITHBANK	26.3	26.65
172	07/03/2023	FLOURMILL	32.95	33.5
173	07/03/2023	ZENITHBANK	25.95	26.65
174	08/03/2023	FLOURMILL	32.95	33.5
175	08/03/2023	ZENITHBANK	25.95	26.65
176	09/03/2023	FLOURMILL	32.95	33.5
177	09/03/2023	ZENITHBANK	26	26.65
178	10/03/2023	FLOURMILL	32.95	33.5
179	10/03/2023	ZENITHBANK	25.8	26.65
180	13/03/2023	FLOURMILL	32.95	33.5
181	13/03/2023	ZENITHBANK	25	26.65
182	14/03/2023	FLOURMILL	32.95	33.5
183	14/03/2023	ZENITHBANK	25	26.65
184	15/03/2023	FLOURMILL	31	33.5
185	15/03/2023	ZENITHBANK	23.85	26.65
186	16/03/2023	FLOURMILL	31	33.5
187	16/03/2023	ZENITHBANK	23.85	26.65
188	17/03/2023	FLOURMILL	31	33.5
189	17/03/2023	ZENITHBANK	24.45	26.65
190	20/03/2023	FLOURMILL	31	33.5
191	20/03/2023	ZENITHBANK	24.75	26.65
192	21/03/2023	FLOURMILL	31	33.5
193	21/03/2023	ZENITHBANK	25.1	26.65
194	22/03/2023	FLOURMILL	31	33.5
195	22/03/2023	ZENITHBANK	25.1	26.65
196	23/03/2023	FLOURMILL	31	33.5
197	23/03/2023	ZENITHBANK	25.1	26.65
198	24/03/2023	FLOURMILL	31	33.5
199	24/03/2023	ZENITHBANK	24.95	26.65
200	27/03/2023	FLOURMILL	31	33.5
201	27/03/2023	ZENITHBANK	25	26.65
202	28/03/2023	FLOURMILL	31	33.5
203	28/03/2023	ZENITHBANK	25	26.65
204	29/03/2023	FLOURMILL	31	33.5
205	29/03/2023	ZENITHBANK	25	26.65
206	30/03/2023	FLOURMILL	31	33.5
207	30/03/2023	ZENITHBANK	25.9	26.65
208	03/04/2023	FLOURMILL	31	33.5
209	03/04/2023	ZENITHBANK	25.8	26.65
210	04/04/2023	FLOURMILL	31	33.5
211	04/04/2023	ZENITHBANK	25.15	26.65
212	05/04/2023	FLOURMILL	29.5	33.5
213	05/04/2023	ZENITHBANK	25.5	26.65
214	06/04/2023	FLOURMILL	29.5	33.5
215	06/04/2023	ZENITHBANK	25.5	26.65
216	11/04/2023	FLOURMILL	29.5	33.5
217	11/04/2023	ZENITHBANK	25.5	26.65
218	12/04/2023	FLOURMILL	29.5	33.5
219	12/04/2023	ZENITHBANK	25.5	26.65
220	13/04/2023	FLOURMILL	29.5	33.5
221	13/04/2023	ZENITHBANK	25.05	26.65
222	14/04/2023	FLOURMILL	29.5	33.5
223	14/04/2023	ZENITHBANK	25	26.65
224	17/04/2023	FLOURMILL	29.5	33.5
225	17/04/2023	ZENITHBANK	22	26.65
226	18/04/2023	FLOURMILL	29.7	33.5
227	18/04/2023	ZENITHBANK	21.8	26.65
228	19/04/2023	FLOURMILL	29.7	33.5
229	19/04/2023	ZENITHBANK	21.85	26.65
230	20/04/2023	FLOURMILL	29.7	33.5
231	20/04/2023	ZENITHBANK	21.95	26.65
232	25/04/2023	FLOURMILL	29.7	33.5
233	25/04/2023	ZENITHBANK	22.05	26.65
234	26/04/2023	FLOURMILL	29.7	33.5
235	26/04/2023	ZENITHBANK	22.55	26.65
236	27/04/2023	FLOURMILL	29.7	33.5
237	27/04/2023	ZENITHBANK	22.8	26.65
238	28/04/2023	FLOURMILL	31	33.5
239	28/04/2023	ZENITHBANK	22.8	26.65
240	02/05/2023	FLOURMILL	31	33.5
241	02/05/2023	ZENITHBANK	23.5	26.65
242	03/05/2023	FLOURMILL	31	33.5
243	03/05/2023	ZENITHBANK	24	26.65
244	04/05/2023	FLOURMILL	31	33.5
245	04/05/2023	ZENITHBANK	24	26.65
246	05/05/2023	FLOURMILL	31	33.5
247	05/05/2023	ZENITHBANK	24	26.65
248	08/05/2023	FLOURMILL	31	33.5
249	08/05/2023	ZENITHBANK	24.05	26.65
250	09/05/2023	FLOURMILL	31.1	33.5
251	09/05/2023	ZENITHBANK	24.5	26.65
252	10/05/2023	FLOURMILL	31.1	33.5
253	10/05/2023	ZENITHBANK	24.4	26.65
254	11/05/2023	FLOURMILL	31.1	33.5
255	11/05/2023	ZENITHBANK	23.9	26.65
256	12/05/2023	FLOURMILL	31.1	33.5
257	12/05/2023	ZENITHBANK	24	26.65
258	15/05/2023	FLOURMILL	31.1	33.5
259	15/05/2023	ZENITHBANK	24.15	26.65
260	16/05/2023	FLOURMILL	31.05	33.5
261	16/05/2023	ZENITHBANK	24.5	26.65
262	17/05/2023	FLOURMILL	31.2	33.5
263	17/05/2023	ZENITHBANK	25.15	26.65
264	18/05/2023	FLOURMILL	31	33.5
265	18/05/2023	ZENITHBANK	25	26.65
266	19/05/2023	FLOURMILL	31	33.5
267	19/05/2023	ZENITHBANK	26	26.65
268	22/05/2023	FLOURMILL	31	33.5
269	22/05/2023	ZENITHBANK	26.45	26.65
270	23/05/2023	FLOURMILL	31.1	33.5
271	23/05/2023	ZENITHBANK	27	27
272	24/05/2023	FLOURMILL	31.1	33.5
273	24/05/2023	ZENITHBANK	26.75	27
274	25/05/2023	FLOURMILL	31.8	33.5
275	25/05/2023	ZENITHBANK	26.75	27
276	26/05/2023	FLOURMILL	31.3	33.5
277	26/05/2023	ZENITHBANK	27	27
278	30/05/2023	FLOURMILL	33.5	33.5
279	30/05/2023	ZENITHBANK	29.7	29.7
280	31/05/2023	FLOURMILL	34.8	34.8
281	31/05/2023	ZENITHBANK	29	29.7
Lists

Cell Formulas
Range	Formula
D2:D281	D2	=MAXIFS(C:C,B:B,B2,A:A,">="&EDATE(A2,-12),A:A,"<="&A2)

all you need to say was "It works" or something similar, not 10,000 characters.

As you new question is significantly different it needs a new thread. Thanks

Hi Fluff,

Please, pardon my ignorance. I'm still learning the rules of the platform.

I have posted another thread as you suggested and I tagged you. Please help, as your solution on this matter is spot on.

Benejindu