How to count occurrences of two or three numbers in different rows?

[kirvist]

After value 4, goes value 2. I looking for formula to count, how many times that occurs in the table. Best, if formula is flexible, where you can input different values. If say, value 7, then value 2. Maybe it is possible to count sequence of three numbers.
Then using =COUNTIFS(B2:B9622,4), result is 535. If using formula =COUNTIFS(B2:B9622,$E$3,B2:B9622,$G$3), where $E$3 = 4, and $G$3 = 2, I get 0.
So, to count how many times combination 4 then 2 occurs, what need to be done?

Thank you

Date	Value
1	1
2	4
3	2
4	7
5	2
6	1
7	1
8	3
9	1
10	18
11	1
12	1
13	1
14	3
15	2
16	3
17	2
18	5
19	1
20	1
21	1
22	1
23	1
24	1
25	1
26	2
27	2
28	5
29	1
30	1
31	1
32	4
33	2
34	11
35	1
36	19
37	1
38	1
39	1
40	6
41	1
42	2
43	2
44	1
45	1
46	3
47	1
48	5
49	1
50	4
51	1
52	18
53	1
54	1
55	1
56	9
57	1
58	5
59	1
60	4
61	1
62	3
63	2
64	2
65	1
66	2
67	1
68	2
69	1
70	1
71	2
72	3
73	1
74	3
75	2
76	7
77	2
78	5
79	1
80	3
81	1
82	2
83	1
84	7
85	1
86	7
87	1
88	7
89	1
90	2
91	3
92	6
93	1
94	3
95	1
96	2
97	1
98	6
99	1
100	9
101	1
102	4
103	1
104	3
105	1
106	1
107	1
108	13
109	1
110	1
111	1
112	6
113	2
114	8
115	1
116	6
117	1
118	16
119	1
120	8
121	1
122	8
123	1
124	1
125	1
126	2
127	1
128	8
129	1
130	1
131	1
132	19
133	2
134	5
135	1
136	2
137	2
138	2
139	1
140	1
141	1
142	6
143	2
144	6
145	1
146	2
147	1
148	5
149	1
150	5
151	1
152	2
153	1
154	21
155	3
156	15
157	1
158	1
159	1
160	1
161	1
162	2
163	2
164	1
165	1
166	3
167	1
168	2
169	1
170	1
171	1
172	2
173	1
174	11
175	1
176	13
177	1
178	5
179	1
180	1
181	2
182	5
183	2
184	9
185	1
186	3
187	2
188	1
189	2
190	13
191	1
192	3
193	1
194	3
195	1
196	1
197	1
198	1
199	1
200	6
201	1
202	2
203	1
204	1
205	2
206	1
207	1
208	3
209	1
210	4
211	2
212	4
213	1
214	9
215	1
216	5
217	1
218	11
219	1
220	1
221	2
222	7
223	3
224	4
225	1
226	2
227	1
228	7
229	1
230	4
231	3
232	16
233	1
234	2
235	2
236	5
237	1
238	1
239	1
240	1
241	2
242	5
243	1
244	6
245	1
246	1
247	2
248	4
249	1
250	10
251	1
252	4
253	1
254	1
255	1
256	1
257	1
258	4
259	1
260	1
261	2
262	1
263	2
264	11
265	1
266	2
267	1
268	2
269	1
270	2
271	1
272	2
273	2
274	2
275	1
276	6
277	1
278	3
279	1
280	4
281	1
282	2
283	1
284	2
285	5
286	4
287	1
288	4
289	1
290	1
291	1
292	3
293	1
294	1
295	1
296	6
297	1
298	7
299	2
300	8
301	1
302	8
303	1
304	3
305	1
306	1
307	2
308	1
309	1
310	5
311	2
312	13
313	1
314	8
315	1
316	2
317	1
318	4
319	1

<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>

<!--[endif]-->

 

[Eric Golf]

Hi,

Try

=COUNTIF(B2:B9622,$E$3)+COUNTIF(B2:B9622,$G$3)

Hope this helps,

Eric.

 

[steve the fish]

You could offset your ranges like this:

=COUNTIFS(B2:B320,E3,B3:B321,G3)

 

[kirvist]

Hi,

Try

=COUNTIF(B2:B9622,$E$3)+COUNTIF(B2:B9622,$G$3)

Hope this helps,

Eric.

Thank you Eric,

Formula returns sum for all count off 4 and 2

kirvis

 

[kirvist]

You could offset your ranges like this:

=COUNTIFS(B2:B320,E3,B3:B321,G3)

Thank you, that works butifully. =COUNTIFS(B1:B9620,$D$2,B2:B9621,$E$2)

Kirvis

 

[Eric Golf]

Hi Kirvis,

Thanks for coming back, glad you got it sorted.

Steve's solution was a new one on me, always learning

Eric