Formulas 1) to return the date of the first occurrence of the highest value; and 2) to count the number of occurrences

[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 will continue to be updated. @Fluff has helped me with a furmula that returns the highest sale quantity (of each of the products) that occurred within the prior year from each day.

Kindly help with:
1) A formula that would return the date that the highest value first occurred.
2) Count the number of times the highest value occurred.

stocke.xlsx
	A	B	C	D	E
1	Date	Company	Close	52 Weeks High	52 Weeks Low
2	11/1/2022	FLOURMILL	30.30
3	11/1/2022	ZENITHBANK	20.00
4	11/2/2022	FLOURMILL	30.30
5	11/2/2022	ZENITHBANK	19.95
6	11/3/2022	FLOURMILL	30.30
7	11/3/2022	ZENITHBANK	19.90
8	11/4/2022	FLOURMILL	30.30
9	11/4/2022	ZENITHBANK	19.85
10	11/7/2022	FLOURMILL	30.30
11	11/7/2022	ZENITHBANK	20.00
12	11/8/2022	FLOURMILL	30.30
13	11/8/2022	ZENITHBANK	19.90
14	11/9/2022	FLOURMILL	30.30
15	11/9/2022	ZENITHBANK	19.95
16	11/10/2022	FLOURMILL	30.30
17	11/10/2022	ZENITHBANK	19.90
18	11/11/2022	FLOURMILL	27.30
19	11/11/2022	ZENITHBANK	19.95
20	11/14/2022	FLOURMILL	27.30
21	11/14/2022	ZENITHBANK	19.95
22	11/15/2022	FLOURMILL	27.30
23	11/15/2022	ZENITHBANK	20.10
24	11/16/2022	FLOURMILL	27.30
25	11/16/2022	ZENITHBANK	21.80
26	11/17/2022	FLOURMILL	27.30
27	11/17/2022	ZENITHBANK	21.30
28	11/18/2022	FLOURMILL	27.30
29	11/18/2022	ZENITHBANK	21.35
30	11/21/2022	FLOURMILL	27.30
31	11/21/2022	ZENITHBANK	21.70
32	11/22/2022	FLOURMILL	27.30
33	11/22/2022	ZENITHBANK	22.10
34	11/23/2022	FLOURMILL	27.30
35	11/23/2022	ZENITHBANK	22.70
36	11/24/2022	FLOURMILL	27.30
37	11/24/2022	ZENITHBANK	22.00
38	11/25/2022	FLOURMILL	28.10
39	11/25/2022	ZENITHBANK	21.90
40	11/28/2022	FLOURMILL	28.10
41	11/28/2022	ZENITHBANK	22.00
42	11/29/2022	FLOURMILL	28.10
43	11/29/2022	ZENITHBANK	21.70
44	11/30/2022	FLOURMILL	28.10
45	11/30/2022	ZENITHBANK	21.90
46	12/1/2022	FLOURMILL	28.10
47	12/1/2022	ZENITHBANK	22.75
48	12/2/2022	FLOURMILL	28.10
49	12/2/2022	ZENITHBANK	22.75
50	12/5/2022	FLOURMILL	28.10
51	12/5/2022	ZENITHBANK	22.20
52	12/6/2022	FLOURMILL	28.10
53	12/6/2022	ZENITHBANK	22.05
54	12/7/2022	FLOURMILL	27.60
55	12/7/2022	ZENITHBANK	22.00
56	12/8/2022	FLOURMILL	27.60
57	12/8/2022	ZENITHBANK	22.05
58	12/9/2022	FLOURMILL	27.60
59	12/9/2022	ZENITHBANK	22.10
60	12/12/2022	FLOURMILL	27.60
61	12/12/2022	ZENITHBANK	22.45
62	12/13/2022	FLOURMILL	27.60
63	12/13/2022	ZENITHBANK	23.00
64	12/14/2022	FLOURMILL	27.60
65	12/14/2022	ZENITHBANK	23.00
66	12/15/2022	FLOURMILL	27.60
67	12/15/2022	ZENITHBANK	23.50
68	12/16/2022	FLOURMILL	27.60
69	12/16/2022	ZENITHBANK	24.10
70	12/19/2022	FLOURMILL	27.60
71	12/19/2022	ZENITHBANK	24.00
72	12/20/2022	FLOURMILL	27.60
73	12/20/2022	ZENITHBANK	24.25
74	12/21/2022	FLOURMILL	27.60
75	12/21/2022	ZENITHBANK	24.30
76	12/22/2022	FLOURMILL	27.60
77	12/22/2022	ZENITHBANK	24.10
78	12/23/2022	FLOURMILL	27.60
79	12/23/2022	ZENITHBANK	24.55
80	12/28/2022	FLOURMILL	27.60
81	12/28/2022	ZENITHBANK	24.90
82	12/29/2022	FLOURMILL	28.40
83	12/29/2022	ZENITHBANK	24.90
84	12/30/2022	FLOURMILL	28.40
85	12/30/2022	ZENITHBANK	24.00
86	1/3/2023	FLOURMILL	28.40
87	1/3/2023	ZENITHBANK	24.00
88	1/4/2023	FLOURMILL	30.00
89	1/4/2023	ZENITHBANK	24.10
90	1/5/2023	FLOURMILL	30.00
91	1/5/2023	ZENITHBANK	24.55
92	1/6/2023	FLOURMILL	29.90
93	1/6/2023	ZENITHBANK	24.45
94	1/9/2023	FLOURMILL	29.00
95	1/9/2023	ZENITHBANK	24.30
96	1/10/2023	FLOURMILL	29.00
97	1/10/2023	ZENITHBANK	24.30
98	1/11/2023	FLOURMILL	29.00
99	1/11/2023	ZENITHBANK	24.40
100	1/12/2023	FLOURMILL	29.00
101	1/12/2023	ZENITHBANK	25.10
102	1/13/2023	FLOURMILL	30.00
103	1/13/2023	ZENITHBANK	25.55
104	1/16/2023	FLOURMILL	30.00
105	1/16/2023	ZENITHBANK	24.60
106	1/17/2023	FLOURMILL	30.90
107	1/17/2023	ZENITHBANK	24.60
108	1/18/2023	FLOURMILL	30.90
109	1/18/2023	ZENITHBANK	24.60
110	1/19/2023	FLOURMILL	30.90
111	1/19/2023	ZENITHBANK	24.55
112	1/20/2023	FLOURMILL	30.90
113	1/20/2023	ZENITHBANK	24.45
114	1/23/2023	FLOURMILL	30.90
115	1/23/2023	ZENITHBANK	24.45
116	1/24/2023	FLOURMILL	30.90
117	1/24/2023	ZENITHBANK	24.50
118	1/25/2023	FLOURMILL	30.90
119	1/25/2023	ZENITHBANK	24.50
120	1/26/2023	FLOURMILL	30.90
121	1/26/2023	ZENITHBANK	24.70
122	1/27/2023	FLOURMILL	30.90
123	1/27/2023	ZENITHBANK	24.70
124	1/30/2023	FLOURMILL	30.90
125	1/30/2023	ZENITHBANK	24.90
126	1/31/2023	FLOURMILL	30.90
127	1/31/2023	ZENITHBANK	25.10
128	2/1/2023	FLOURMILL	30.00
129	2/1/2023	ZENITHBANK	25.50
130	2/2/2023	FLOURMILL	30.00
131	2/2/2023	ZENITHBANK	25.50
132	2/3/2023	FLOURMILL	30.00
133	2/3/2023	ZENITHBANK	25.10
134	2/6/2023	FLOURMILL	29.95
135	2/6/2023	ZENITHBANK	25.10
136	2/7/2023	FLOURMILL	29.95
137	2/7/2023	ZENITHBANK	25.00
138	2/8/2023	FLOURMILL	29.95
139	2/8/2023	ZENITHBANK	25.20
140	2/9/2023	FLOURMILL	29.95
141	2/9/2023	ZENITHBANK	25.10
142	2/10/2023	FLOURMILL	29.95
143	2/10/2023	ZENITHBANK	25.10
144	2/14/2023	FLOURMILL	30.00
145	2/14/2023	ZENITHBANK	25.30
146	2/15/2023	FLOURMILL	30.00
147	2/15/2023	ZENITHBANK	25.35
148	2/16/2023	FLOURMILL	30.00
149	2/16/2023	ZENITHBANK	25.50
150	2/17/2023	FLOURMILL	30.00
151	2/17/2023	ZENITHBANK	25.50
152	2/20/2023	FLOURMILL	30.90
153	2/20/2023	ZENITHBANK	25.00
154	2/21/2023	FLOURMILL	30.90
155	2/21/2023	ZENITHBANK	25.00
156	2/22/2023	FLOURMILL	31.00
157	2/22/2023	ZENITHBANK	25.20
158	2/23/2023	FLOURMILL	31.00
159	2/23/2023	ZENITHBANK	25.35
160	2/24/2023	FLOURMILL	32.05
161	2/24/2023	ZENITHBANK	25.75
162	2/28/2023	FLOURMILL	32.00
163	2/28/2023	ZENITHBANK	26.60
164	3/1/2023	FLOURMILL	32.00
165	3/1/2023	ZENITHBANK	26.65
166	3/2/2023	FLOURMILL	32.00
167	3/2/2023	ZENITHBANK	26.40
168	3/3/2023	FLOURMILL	33.50
169	3/3/2023	ZENITHBANK	26.60
170	3/6/2023	FLOURMILL	32.95
171	3/6/2023	ZENITHBANK	26.30
172	3/7/2023	FLOURMILL	32.95
173	3/7/2023	ZENITHBANK	25.95
174	3/8/2023	FLOURMILL	32.95
175	3/8/2023	ZENITHBANK	25.95
176	3/9/2023	FLOURMILL	32.95
177	3/9/2023	ZENITHBANK	26.00
178	3/10/2023	FLOURMILL	32.95
179	3/10/2023	ZENITHBANK	25.80
180	3/13/2023	FLOURMILL	32.95
181	3/13/2023	ZENITHBANK	25.00
182	3/14/2023	FLOURMILL	32.95
183	3/14/2023	ZENITHBANK	25.00
184	3/15/2023	FLOURMILL	31.00
185	3/15/2023	ZENITHBANK	23.85
186	3/16/2023	FLOURMILL	31.00
187	3/16/2023	ZENITHBANK	23.85
188	3/17/2023	FLOURMILL	31.00
189	3/17/2023	ZENITHBANK	24.45
190	3/20/2023	FLOURMILL	31.00
191	3/20/2023	ZENITHBANK	24.75
192	3/21/2023	FLOURMILL	31.00
193	3/21/2023	ZENITHBANK	25.10
194	3/22/2023	FLOURMILL	31.00
195	3/22/2023	ZENITHBANK	25.10
196	3/23/2023	FLOURMILL	31.00
197	3/23/2023	ZENITHBANK	25.10
198	3/24/2023	FLOURMILL	31.00
199	3/24/2023	ZENITHBANK	24.95
200	3/27/2023	FLOURMILL	31.00
201	3/27/2023	ZENITHBANK	25.00
202	3/28/2023	FLOURMILL	31.00
203	3/28/2023	ZENITHBANK	25.00
204	3/29/2023	FLOURMILL	31.00
205	3/29/2023	ZENITHBANK	25.00
206	3/30/2023	FLOURMILL	31.00
207	3/30/2023	ZENITHBANK	25.90
208	4/3/2023	FLOURMILL	31.00
209	4/3/2023	ZENITHBANK	25.80
210	4/4/2023	FLOURMILL	31.00
211	4/4/2023	ZENITHBANK	25.15
212	4/5/2023	FLOURMILL	29.50
213	4/5/2023	ZENITHBANK	25.50
214	4/6/2023	FLOURMILL	29.50
215	4/6/2023	ZENITHBANK	25.50
216	4/11/2023	FLOURMILL	29.50
217	4/11/2023	ZENITHBANK	25.50
218	4/12/2023	FLOURMILL	29.50
219	4/12/2023	ZENITHBANK	25.50
220	4/13/2023	FLOURMILL	29.50
221	4/13/2023	ZENITHBANK	25.05
222	4/14/2023	FLOURMILL	29.50
223	4/14/2023	ZENITHBANK	25.00
224	4/17/2023	FLOURMILL	29.50
225	4/17/2023	ZENITHBANK	22.00
226	4/18/2023	FLOURMILL	29.70
227	4/18/2023	ZENITHBANK	21.80
228	4/19/2023	FLOURMILL	29.70
229	4/19/2023	ZENITHBANK	21.85
230	4/20/2023	FLOURMILL	29.70
231	4/20/2023	ZENITHBANK	21.95
232	4/25/2023	FLOURMILL	29.70
233	4/25/2023	ZENITHBANK	22.05
234	4/26/2023	FLOURMILL	29.70
235	4/26/2023	ZENITHBANK	22.55
236	4/27/2023	FLOURMILL	29.70
237	4/27/2023	ZENITHBANK	22.80
238	4/28/2023	FLOURMILL	31.00
239	4/28/2023	ZENITHBANK	22.80
240	5/2/2023	FLOURMILL	31
241	5/2/2023	ZENITHBANK	23.5
242	5/3/2023	FLOURMILL	31
243	5/3/2023	ZENITHBANK	24
244	5/4/2023	FLOURMILL	31
245	5/4/2023	ZENITHBANK	24
246	5/5/2023	FLOURMILL	31
247	5/5/2023	ZENITHBANK	24
248	5/8/2023	FLOURMILL	31
249	5/8/2023	ZENITHBANK	24.05
250	5/9/2023	FLOURMILL	31.1
251	5/9/2023	ZENITHBANK	24.5
252	5/10/2023	FLOURMILL	31.1
253	5/10/2023	ZENITHBANK	24.4
254	5/11/2023	FLOURMILL	31.1
255	5/11/2023	ZENITHBANK	23.9
256	5/12/2023	FLOURMILL	31.1
257	5/12/2023	ZENITHBANK	24
258	5/15/2023	FLOURMILL	31.1
259	5/15/2023	ZENITHBANK	24.15
260	5/16/2023	FLOURMILL	31.05
261	5/16/2023	ZENITHBANK	24.5
262	5/17/2023	FLOURMILL	31.2
263	5/17/2023	ZENITHBANK	25.15
264	5/18/2023	FLOURMILL	31
265	5/18/2023	ZENITHBANK	25
266	5/19/2023	FLOURMILL	31
267	5/19/2023	ZENITHBANK	26
268	5/22/2023	FLOURMILL	31
269	5/22/2023	ZENITHBANK	26.45
270	5/23/2023	FLOURMILL	31.1
271	5/23/2023	ZENITHBANK	27
272	5/24/2023	FLOURMILL	31.1
273	5/24/2023	ZENITHBANK	26.75
274	5/25/2023	FLOURMILL	31.8
275	5/25/2023	ZENITHBANK	26.75
276	5/26/2023	FLOURMILL	31.3
277	5/26/2023	ZENITHBANK	27
278	5/30/2023	FLOURMILL	33.5
279	5/30/2023	ZENITHBANK	29.7
280	5/31/2023	FLOURMILL	34.8
281	5/31/2023	ZENITHBANK	29
Sheet2

 

[Eric W]

Try:

Book1
	A	B	C	D	E	F	G	H	I	J
1	Date	Company	Close	52 Weeks High	52 Weeks Low		Companies	High	First time	# of times
2	11/1/2022	FLOURMILL	30.3				FLOURMILL	34.8	5/31/2023	1
3	11/1/2022	ZENITHBANK	20				ZENITHBANK	29.7	5/30/2023	1
4	11/2/2022	FLOURMILL	30.3
5	11/2/2022	ZENITHBANK	19.95
6	11/3/2022	FLOURMILL	30.3
7	11/3/2022	ZENITHBANK	19.9
8	11/4/2022	FLOURMILL	30.3
9	11/4/2022	ZENITHBANK	19.85
10	11/7/2022	FLOURMILL	30.3
11	11/7/2022	ZENITHBANK	20
12	11/8/2022	FLOURMILL	30.3
13	11/8/2022	ZENITHBANK	19.9
14	11/9/2022	FLOURMILL	30.3
15	11/9/2022	ZENITHBANK	19.95
16	11/10/2022	FLOURMILL	30.3
17	11/10/2022	ZENITHBANK	19.9
18	11/11/2022	FLOURMILL	27.3
19	11/11/2022	ZENITHBANK	19.95
20	11/14/2022	FLOURMILL	27.3
21	11/14/2022	ZENITHBANK	19.95
Sheet2

Cell Formulas
Range	Formula
G2:G3	G2	=UNIQUE(B2:B281)
H2:H3	H2	=MAXIFS(C2:C281,B2:B281,G2#)
J2:J3	J2	=COUNTIFS(B2:B281,G2#,C2:C281,H2#)
I2:I3	I2	=MIN(FILTER(A$2:A$281,(B$2:B$281=G2)*(C$2:C$281=H2)))
Dynamic array formulas.

 

[benejindu]

Try:

Book1
	A	B	C	D	E	F	G	H	I	J
1	Date	Company	Close	52 Weeks High	52 Weeks Low		Companies	High	First time	# of times
2	11/1/2022	FLOURMILL	30.3				FLOURMILL	34.8	5/31/2023	1
3	11/1/2022	ZENITHBANK	20				ZENITHBANK	29.7	5/30/2023	1
4	11/2/2022	FLOURMILL	30.3
5	11/2/2022	ZENITHBANK	19.95
6	11/3/2022	FLOURMILL	30.3
7	11/3/2022	ZENITHBANK	19.9
8	11/4/2022	FLOURMILL	30.3
9	11/4/2022	ZENITHBANK	19.85
10	11/7/2022	FLOURMILL	30.3
11	11/7/2022	ZENITHBANK	20
12	11/8/2022	FLOURMILL	30.3
13	11/8/2022	ZENITHBANK	19.9
14	11/9/2022	FLOURMILL	30.3
15	11/9/2022	ZENITHBANK	19.95
16	11/10/2022	FLOURMILL	30.3
17	11/10/2022	ZENITHBANK	19.9
18	11/11/2022	FLOURMILL	27.3
19	11/11/2022	ZENITHBANK	19.95
20	11/14/2022	FLOURMILL	27.3
21	11/14/2022	ZENITHBANK	19.95
Sheet2

Cell Formulas
Range	Formula
G2:G3	G2	=UNIQUE(B2:B281)
H2:H3	H2	=MAXIFS(C2:C281,B2:B281,G2#)
J2:J3	J2	=COUNTIFS(B2:B281,G2#,C2:C281,H2#)
I2:I3	I2	=MIN(FILTER(A$2:A$281,(B$2:B$281=G2)*(C$2:C$281=H2)))
Dynamic array formulas.

Hi Eric, Thanks so much for your great help.

1) =MIN(FILTER(A$2:A$281,(B$2:B$281=G2)*(C$2:C$281=H2)))
In the actual workbook, the formula returns a date that is OLDER than 1 year from today. My objective is to get the date on which the highest value for each product occurred within one year prior to today (or any other date).

2) =COUNTIFS(B2:B281,G2#,C2:C281,H2#)
In the actual worksheet, this formula returns zero, despite the fact that there are values that meet the criteria.

Benejindu

 

[Eric W]

1) To handle a starting date, adjust the formulas as follows:

Book1
	G	H	I	J	K
1	Companies	High	First time	# of times	After:
2	FLOURMILL	34.8	5/31/2023	1	6/5/2022
3	ZENITHBANK	29.7	5/30/2023	1
Sheet2

Cell Formulas
Range	Formula
G2:G3	G2	=UNIQUE(B2:B281)
H2:H3	H2	=MAXIFS(C2:C281,B2:B281,G2#,A2:A281,">"&K2)
J2:J3	J2	=COUNTIFS(B2:B281,G2#,C2:C281,H2#,A2:A281,">"&K2)
K2	K2	=TODAY()-365
I2	I2	=MIN(FILTER(A$2:A$281,(A$2:A$281>K2)*(B$2:B$281=G2)*(C$2:C$281=H2)))
I3	I3	=MIN(FILTER(A$2:A$281,(B$2:B$281=G3)*(C$2:C$281=H3)))
Dynamic array formulas.

You can incorporate the date in the formulas if you want.

2) It should return at least one, since it should match the maximum value found. As for the rest, it's possible you're encountering the dreaded binary/decimal rounding error problem. In short, Excel stores numbers in a binary format (all 1s and 0s), but displays them in a decimal format. Integers work fine, decimals often are rounded in about the 14th digit which makes it so there's no match. There are several ways to handle this, but probably the easiest is just to count the numbers that are "close enough". You seem to be showing 2 decimals, so if we count anything that is within .01, then that should match what you'd see if you counted them by eye. Change the J2 formula to:

=COUNTIFS(B2:B281,G2#,C2:C281,">"&H2#-0.01,A2:A281,">"&K2)

 

[benejindu]

1) To handle a starting date, adjust the formulas as follows:

Book1
	G	H	I	J	K
1	Companies	High	First time	# of times	After:
2	FLOURMILL	34.8	5/31/2023	1	6/5/2022
3	ZENITHBANK	29.7	5/30/2023	1
Sheet2

Cell Formulas
Range	Formula
G2:G3	G2	=UNIQUE(B2:B281)
H2:H3	H2	=MAXIFS(C2:C281,B2:B281,G2#,A2:A281,">"&K2)
J2:J3	J2	=COUNTIFS(B2:B281,G2#,C2:C281,H2#,A2:A281,">"&K2)
K2	K2	=TODAY()-365
I2	I2	=MIN(FILTER(A$2:A$281,(A$2:A$281>K2)*(B$2:B$281=G2)*(C$2:C$281=H2)))
I3	I3	=MIN(FILTER(A$2:A$281,(B$2:B$281=G3)*(C$2:C$281=H3)))
Dynamic array formulas.

You can incorporate the date in the formulas if you want.

2) It should return at least one, since it should match the maximum value found. As for the rest, it's possible you're encountering the dreaded binary/decimal rounding error problem. In short, Excel stores numbers in a binary format (all 1s and 0s), but displays them in a decimal format. Integers work fine, decimals often are rounded in about the 14th digit which makes it so there's no match. There are several ways to handle this, but probably the easiest is just to count the numbers that are "close enough". You seem to be showing 2 decimals, so if we count anything that is within .01, then that should match what you'd see if you counted them by eye. Change the J2 formula to:

=COUNTIFS(B2:B281,G2#,C2:C281,">"&H2#-0.01,A2:A281,">"&K2)

Thank you so much @Eric W ! It worked!
One thing, though: I don't understand why you had to put the # symbol. The COUNTIFS wouldn't work till I removed # from the formula. Please, what's the # supposed to do?
Benejindu

 

[Eric W]

Starting a few years back, Microsoft introduced some dynamic array functions, like UNIQUE. If you use one of them, and the results take more than one cell, the results will SPILL down the column as far as necessary. If you want to refer to the entire array of results, you reference the cell with the formula in it, and the put the # on the end. So the G2 formula has 2 results, so G2# refers to G2:G3. The real benefit of this is if you use the array form (G2#) in other formulas, then they will SPILL also. I don't know why it didn't work for you. You can still do things the old fashioned way though, which is what you ended up doing. For example:

Book1
	G	H	I	J	K
1	Companies	High	First time	# of times	After:
2	FLOURMILL	34.8	5/31/2023	1	6/5/2022
3	ZENITHBANK	29.7	5/30/2023	1
Sheet2

Cell Formulas
Range	Formula
G2:G3	G2	=UNIQUE(B2:B281)
H2:H3	H2	=MAXIFS(C$2:C$281,B$2:B$281,G2,A$2:A$281,">"&K$2)
I2:I3	I2	=MIN(FILTER(A$2:A$281,(A$2:A$281>K$2)*(B$2:B$281=G2)*(C$2:C$281>H2-0.01)))
J2:J3	J2	=COUNTIFS(B$2:B$281,G2,C$2:C$281,">"&H2-0.01,A$2:A$281,">"&K$2)
K2	K2	=TODAY()-365
Dynamic array formulas.

2 things to keep in mind, If you do it this way, you need to drag the formulas down the column yourself as far as needed. And second, if you do that, then Excel will automatically update the range references for you, so you'll need to use the $ symbol to make sure the ranges in the formulas stay the same as you drag them down. See how I did it above. (I also tweaked the I2 formula a bit to handle an issue I just thought of.)

Glad I could help!