How to count consecutive values with certain threshold

[sdatrmc]

Sir
I have a row having values 1, 1.2, 5, 6, 9, 2, 3, 6, 8, 12, 3, 4, 1, 8, 14.2, 7, 11.9
How to count consecutive cells with values greater than 5 with frequency 2, 3 and more than 3 in C18, C19, C20 and sum of those values in C21, C22, and C23 respectively ? Image of data set with desired results is attached.

Thanks in advance.

 

[Eric W]

Try:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
1																				consecutive 2 cells with values >5	Consecutive 3 cells with values >5	consecutive >3 cells with values >5
2		1	1.2	5	6	9	2	3	6	8	12	3	4	1	8	14.2	7	9		1	1	1	15	26	38.2
Sheet20

Cell Formulas
Range	Formula
T2	T2	=SUM(--(FREQUENCY(IF($B$2:$R$2>5,COLUMN($B$2:$R$2)),IF($B$2:$R$2<=5,COLUMN($B$2:$R$2)))=2))
U2	U2	=SUM(--(FREQUENCY(IF($B$2:$R$2>5,COLUMN($B$2:$R$2)),IF($B$2:$R$2<=5,COLUMN($B$2:$R$2)))=3))
V2	V2	=SUM(--(FREQUENCY(IF($B$2:$R$2>5,COLUMN($B$2:$R$2)),IF($B$2:$R$2<=5,COLUMN($B$2:$R$2)))>3))
W2	W2	=SUM(IF((A2:P2<=5)*(B2:Q2>5)*(C2:R2>5)*(D2:S2<=5),B2:Q2+C2:R2))
X2	X2	=SUM(IF((A2:O2<=5)*(B2:P2>5)*(C2:Q2>5)*(D2:R2>5)*(E2:S2<=5),B2:P2+C2:Q2+D2:R2))
Y2	Y2	=SUM(B2:R2)-W2-X2-SUMIF(B2:R2,"<=5")-SUM(IF((A2:Q2<=5)*(B2:R2>5)*(C2:S2<=5),B2:R2))
Press CTRL+SHIFT+ENTER to enter array formulas.

I had to add an empty column before and after your data to get the W2, X2, and Y2 formulas to work.

 

[0x33]

Try:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
1																				consecutive 2 cells with values >5	Consecutive 3 cells with values >5	consecutive >3 cells with values >5
2		1	1.2	5	6	9	2	3	6	8	12	3	4	1	8	14.2	7	9		1	1	1	15	26	38.2
Sheet20

Cell Formulas
Range	Formula
T2	T2	=SUM(--(FREQUENCY(IF($B$2:$R$2>5,COLUMN($B$2:$R$2)),IF($B$2:$R$2<=5,COLUMN($B$2:$R$2)))=2))
U2	U2	=SUM(--(FREQUENCY(IF($B$2:$R$2>5,COLUMN($B$2:$R$2)),IF($B$2:$R$2<=5,COLUMN($B$2:$R$2)))=3))
V2	V2	=SUM(--(FREQUENCY(IF($B$2:$R$2>5,COLUMN($B$2:$R$2)),IF($B$2:$R$2<=5,COLUMN($B$2:$R$2)))>3))
W2	W2	=SUM(IF((A2:P2<=5)*(B2:Q2>5)*(C2:R2>5)*(D2:S2<=5),B2:Q2+C2:R2))
X2	X2	=SUM(IF((A2:O2<=5)*(B2:P2>5)*(C2:Q2>5)*(D2:R2>5)*(E2:S2<=5),B2:P2+C2:Q2+D2:R2))
Y2	Y2	=SUM(B2:R2)-W2-X2-SUMIF(B2:R2,"<=5")-SUM(IF((A2:Q2<=5)*(B2:R2>5)*(C2:S2<=5),B2:R2))
Press CTRL+SHIFT+ENTER to enter array formulas.

I had to add an empty column before and after your data to get the W2, X2, and Y2 formulas to work.

Hello, and sorry for hijacking this thread. But I was working on a similar issue when I came across your response in both of them. I don't know if it would've been better to post a new thread or try to reach you here since the post is somewhat recent.

Below is the dataset I'm working with. And there are two things I'm trying to amend for it to work. My objective is to find if employee is eligible for full time which is 3 weeks with 32 hours or three consecutive values where the sum is greater than 92 hours for a specific Emp ID. Yellow is the functions I was working (K12 being final version) on using your information. My old one was just a countifs in blue.

1. The data I'm looking at is in column base vs row base. I was having a particular issue with the SUM of Consecutive Cells Total from here thread. I couldn't adjust the COLUMNS to ROWs, or delete the TRANSPONSE since I thought maybe it wasn't nessesary since my data is already vertical. Also I don't know if I could use because of the OFFSET.
2. I'm also trying to get the average of the 3 consecutive values. I don't know if that is possible with what is currently made.

chkhist3 2020.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1												10/1/2020
2		Check #	Date	Emp#	Name	Emp ID	Amount	Hours			A00852	3
3		999999	10/16/2020	A00852	Doe, John	211065	0.00	32
4		999999	10/23/2020	A00852	Doe, John	211065	0.00	40			8
5		999999	10/30/2020	A00852	Doe, John	211065	0.00	40
6		999999	11/6/2020	A00852	Doe, John	211065	0.00	20
7		999999	11/13/2020	A00852	Doe, John	211065	0.00	40			12
8		999999	11/20/2020	A00852	Doe, John	211065	0.00	40			5
9		999999	11/27/2020	A00852	Doe, John	211065	0.00	0
10		999999	12/4/2020	A00852	Doe, John	211065	0.00	0
11		999999	12/11/2020	A00852	Doe, John	211065	0.00	0			1
12		999999	12/18/2020	A00852	Doe, John	211065	0.00	0			3
13		999999	12/24/2020	A00852	Doe, John	211065	0.00	0
14		999999	12/31/2020	A00852	Doe, John	211065	0.00	0
15		999999	2/21/2020	ABA064	Doe, John Jr	203734	0.00	32
16		999999	2/28/2020	ABA064	Doe, John Jr	203734	0.00	0
17		999999	3/6/2020	ABA064	Doe, John Jr	203734	0.00	0
18		999999	3/13/2020	ABA064	Doe, John Jr	203734	0.00	20
19		999999	3/20/2020	ABA064	Doe, John Jr	203734	0.00	40
20		999999	3/27/2020	ABA064	Doe, John Jr	203734	0.00	40
checkhist

Cell Formulas
Range	Formula
L2	L2	=COUNTIFS( checkhist!$C$3:$C$382768,">="&$L$1, checkhist!$C$3:$C$382768,"<="&EOMONTH($L$1,0), checkhist!$D$3:$D$382768,$K2, checkhist!$H$3:$H$382768,">="&32 )
K4	K4	=SUM( --(FREQUENCY( IF(H3:H20>=32,COLUMN(H3:H20)), IF(H3:H20<=32,COLUMN(H3:H20))) ) )
K7	K7	=SUM(--(FREQUENCY(IF(D3:D20=K2,H3:H20),H3:H20)))
K8	K8	=SUM(--(FREQUENCY(IF((D3:D20=K2)*(H3:H20>=32), H3:H20), H3:H20)) )
K11	K11	=SUM(IF(FREQUENCY(IF(H3:H20>31,ROW(H3:H20)),IF(H3:H20=31,ROW(H3:H20)))>=3,1))
K12	K12	=MAX(FREQUENCY(IF((D3:D20=K2)*(H3:H20>31),ROW(H3:H20)),IF(H3:H20<=31,ROW(H3:H20))))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Eric W]

Welcome to the forum!

Yes, it probably would have been better to open a new thread, more people would have seen it.

I'm not entirely clear on what you want. If you want to find out how many weeks there are, with the right Emp#, and the right date range, that are over 32 hours, then your L2 formula should work fine. If you want to find out the maximum 3 week sum, satisfying all those conditions, then try:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1												10/1/2020
2		Check #	Date	Emp#	Name	Emp ID	Amount	Hours			A00852	3
3		999999	10/16/2020	A00852	Doe, John	211065	0	32
4		999999	10/23/2020	A00852	Doe, John	211065	0	40			8	112
5		999999	10/30/2020	A00852	Doe, John	211065	0	40
6		999999	11/6/2020	A00852	Doe, John	211065	0	20
7		999999	11/13/2020	A00852	Doe, John	211065	0	40			12
8		999999	11/20/2020	A00852	Doe, John	211065	0	40			5
9		999999	11/27/2020	A00852	Doe, John	211065	0	0
10		999999	12/4/2020	A00852	Doe, John	211065	0	0
11		999999	12/11/2020	A00852	Doe, John	211065	0	0			1
12		999999	12/18/2020	A00852	Doe, John	211065	0	0			3
13		999999	12/24/2020	A00852	Doe, John	211065	0	0
14		999999	12/31/2020	A00852	Doe, John	211065	0	0
15		999999	2/21/2020	ABA064	Doe, John Jr	203734	0	32
16		999999	2/28/2020	ABA064	Doe, John Jr	203734	0	0
17		999999	3/6/2020	ABA064	Doe, John Jr	203734	0	0
18		999999	3/13/2020	ABA064	Doe, John Jr	203734	0	20
19		999999	3/20/2020	ABA064	Doe, John Jr	203734	0	40
20		999999	3/27/2020	ABA064	Doe, John Jr	203734	0	40
checkhist

Cell Formulas
Range	Formula
L2	L2	=COUNTIFS( checkhist!$C$3:$C$382768,">="&$L$1, checkhist!$C$3:$C$382768,"<="&EOMONTH($L$1,0), checkhist!$D$3:$D$382768,$K2, checkhist!$H$3:$H$382768,">="&32 )
L4	L4	=AGGREGATE(14,6,SUMIFS(OFFSET(H3,ROW(H3:H20)-ROW(H3),0,3),OFFSET(D3,ROW(D3:D20)-ROW(D3),0,3),K2,OFFSET(C3,ROW(C3:C20)-ROW(C3),0,3),">="&L1,OFFSET(C3,ROW(C3:C20)-ROW(C3),0,3),"<="&EOMONTH(L1,0)),1)

If you want the average, divide by 3. This formula in essence does a SUMIFS on every 3 row range in your table. Finding the average of each of those is tricky, since each range could have 0-3 rows with matching data.