How to count consecutive values with certain threshold

sdatrmc

New Member
Joined
Jan 30, 2012
Messages
8
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.
 

Attachments

  • 1.JPG
    1.JPG
    29.4 KB · Views: 27

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,739
Try:

Book1
ABCDEFGHIJKLMNOPQRSTUVWXY
1consecutive 2 cells with values >5Consecutive 3 cells with values >5consecutive >3 cells with values >5
211.2569236812341814.279111152638.2
Sheet20
Cell Formulas
RangeFormula
T2T2=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))
U2U2=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))
V2V2=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))
W2W2=SUM(IF((A2:P2<=5)*(B2:Q2>5)*(C2:R2>5)*(D2:S2<=5),B2:Q2+C2:R2))
X2X2=SUM(IF((A2:O2<=5)*(B2:P2>5)*(C2:Q2>5)*(D2:R2>5)*(E2:S2<=5),B2:P2+C2:Q2+D2:R2))
Y2Y2=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

New Member
Joined
Jan 18, 2021
Messages
1
Office Version
  1. 2019
Platform
  1. Windows
Try:

Book1
ABCDEFGHIJKLMNOPQRSTUVWXY
1consecutive 2 cells with values >5Consecutive 3 cells with values >5consecutive >3 cells with values >5
211.2569236812341814.279111152638.2
Sheet20
Cell Formulas
RangeFormula
T2T2=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))
U2U2=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))
V2V2=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))
W2W2=SUM(IF((A2:P2<=5)*(B2:Q2>5)*(C2:R2>5)*(D2:S2<=5),B2:Q2+C2:R2))
X2X2=SUM(IF((A2:O2<=5)*(B2:P2>5)*(C2:Q2>5)*(D2:R2>5)*(E2:S2<=5),B2:P2+C2:Q2+D2:R2))
Y2Y2=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
ABCDEFGHIJKL
110/1/2020
2Check #DateEmp#NameEmp IDAmountHoursA008523
399999910/16/2020A00852Doe, John2110650.0032
499999910/23/2020A00852Doe, John2110650.00408
599999910/30/2020A00852Doe, John2110650.0040
699999911/6/2020A00852Doe, John2110650.0020
799999911/13/2020A00852Doe, John2110650.004012
899999911/20/2020A00852Doe, John2110650.00405
999999911/27/2020A00852Doe, John2110650.000
1099999912/4/2020A00852Doe, John2110650.000
1199999912/11/2020A00852Doe, John2110650.0001
1299999912/18/2020A00852Doe, John2110650.0003
1399999912/24/2020A00852Doe, John2110650.000
1499999912/31/2020A00852Doe, John2110650.000
159999992/21/2020ABA064Doe, John Jr2037340.0032
169999992/28/2020ABA064Doe, John Jr2037340.000
179999993/6/2020ABA064Doe, John Jr2037340.000
189999993/13/2020ABA064Doe, John Jr2037340.0020
199999993/20/2020ABA064Doe, John Jr2037340.0040
209999993/27/2020ABA064Doe, John Jr2037340.0040
checkhist
Cell Formulas
RangeFormula
L2L2=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 )
K4K4=SUM( --(FREQUENCY( IF(H3:H20>=32,COLUMN(H3:H20)), IF(H3:H20<=32,COLUMN(H3:H20))) ) )
K7K7=SUM(--(FREQUENCY(IF(D3:D20=K2,H3:H20),H3:H20)))
K8K8=SUM(--(FREQUENCY(IF((D3:D20=K2)*(H3:H20>=32), H3:H20), H3:H20)) )
K11K11=SUM(IF(FREQUENCY(IF(H3:H20>31,ROW(H3:H20)),IF(H3:H20=31,ROW(H3:H20)))>=3,1))
K12K12=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

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,739
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
ABCDEFGHIJKL
110/1/2020
2Check #DateEmp#NameEmp IDAmountHoursA008523
399999910/16/2020A00852Doe, John211065032
499999910/23/2020A00852Doe, John2110650408112
599999910/30/2020A00852Doe, John211065040
699999911/6/2020A00852Doe, John211065020
799999911/13/2020A00852Doe, John21106504012
899999911/20/2020A00852Doe, John2110650405
999999911/27/2020A00852Doe, John21106500
1099999912/4/2020A00852Doe, John21106500
1199999912/11/2020A00852Doe, John211065001
1299999912/18/2020A00852Doe, John211065003
1399999912/24/2020A00852Doe, John21106500
1499999912/31/2020A00852Doe, John21106500
159999992/21/2020ABA064Doe, John Jr203734032
169999992/28/2020ABA064Doe, John Jr20373400
179999993/6/2020ABA064Doe, John Jr20373400
189999993/13/2020ABA064Doe, John Jr203734020
199999993/20/2020ABA064Doe, John Jr203734040
209999993/27/2020ABA064Doe, John Jr203734040
checkhist
Cell Formulas
RangeFormula
L2L2=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 )
L4L4=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.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,998
Messages
5,622,096
Members
415,876
Latest member
csibonga2k17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top