Counting the number of instances where criteria are met with a minimum of consecutive rows.

[FWE_EXCEL]

Hello,

I'm a first-time poster so please excuse any mistakes. I have a problem regarding finding a number value for how often a combination of specific criteria is met.

My data is organized in multiple rows, where each downward cell represents 1 hour. The data for criteria 1 (hs <= 1) is located in the B row, while the data for criteria 2 (tp <=10) is located in the C row.

I need to find how many times Criteria 1 (I4) & 2 (I5) are met in a minimum of 6 (I6) consecutive rows in order to determine how many times a suitable operation window is open.

I have tried to use variations of COUNTIF, IF, SUMPRODUCT, and other functions, but had no success yet.

Thanks in advance!

 

[mackc557]

So your column B is a count of the number of waves below the height or is a wave height? Column C is a count or a duration? Are you looking for an Excel formula, a VBA based function, or a VBA Macro?

 

[FWE_EXCEL]

So your column B is a count of the number of waves below the height or is a wave height? Column C is a count or a duration? Are you looking for an Excel formula, a VBA based function, or a VBA Macro?

Thank you for the reply,

To clarify, column B is a specific wave height (hs) that is recorded on the specific timestamp (column A). Column C is the timeperiod of the wave. Im looking for a Excel Formula if thats possible.

 

[mackc557]

This requires a few more things added to your sheet, but it's not too bad. Also it makes it easily customizable

In the below locations (you can adjust the locations as needed):
G2 is a formula to check if both the wave height and wave period match your requirements (you will need to expand it to every row you are calculating for)
J10 requires your input for the column name where you put the G2 formula
J11 requires your input for the first row in which contains the G2 formula
Those are the only 3 that require you to enter data or expand to keep up with your sheet.


As for the rest, they calculate on their own.
I8 is your desired value
J13 calculates the last row used for the G2 formula (it will update if you change J2)
J14 is the range location of all your G2 formula
J15 is a string representation of what the I8 formula is looking for

Book1.xlsm
	A	B	C	D	E	F	G	H	I	J
1	time	hs	tp	hs_swell	tp_swell
2	1.1.18 12:00	0.8545	14.8642				0
3	1.1.18 13:00	0.8398	14.8642				0	Criteria
4	1.1.18 14:00	0.8245	14.8642				0	Wave Height Max	1	m
5	1.1.18 15:00	0.8132	14.8642				0	Wave Period Max	12	s
6	1.1.18 16:00	0.8058	14.8642				0	Duration of Window	6	hours
7	1.1.18 17:00	0.7979	13.5129				0
8	1.1.18 18:00	0.7913	13.5129				0	Number of Available Windows	2
9	1.1.18 19:00	0.7574	13.5129				0
10	1.1.18 20:00	0.7874	13.5129				0		Column	G
11	1.1.18 21:00	0.7864	13.5129				0		Start Row	2
12	1.1.18 22:00	0.7803	12.2845				0
13	1.1.18 23:00	0.7698	12.2845				0		Ending Row	75
14	1.2.18 0:00	0.7538	12.2845				0		Range	G2:G75
15	1.2.18 1:00	0.7377	12.2845				0		Search Value	0111111
Sheet3

Cell Formulas
Range	Formula
I8	I8	=(LEN(CONCAT(INDIRECT(J14)))-LEN(SUBSTITUTE(CONCAT(INDIRECT(J14)),J15,"")))/(I6+1)
G2:G15	G2	=IF(AND(C2<$I$5,B2<$I$4),1,0)
J13	J13	=LOOKUP(2,1/(INDIRECT(J10&":"&J10)<>""),ROW(INDIRECT(J10&":"&J10)))
J14	J14	=J10&J11&":"&J10&J13
J15	J15	="0" & REPT("1",I6)

Hope it helps!

 

[FWE_EXCEL]

This requires a few more things added to your sheet, but it's not too bad. Also it makes it easily customizable

In the below locations (you can adjust the locations as needed):
G2 is a formula to check if both the wave height and wave period match your requirements (you will need to expand it to every row you are calculating for)
J10 requires your input for the column name where you put the G2 formula
J11 requires your input for the first row in which contains the G2 formula
Those are the only 3 that require you to enter data or expand to keep up with your sheet.


As for the rest, they calculate on their own.
I8 is your desired value
J13 calculates the last row used for the G2 formula (it will update if you change J2)
J14 is the range location of all your G2 formula
J15 is a string representation of what the I8 formula is looking for

Book1.xlsm
	A	B	C	D	E	F	G	H	I	J
1	time	hs	tp	hs_swell	tp_swell
2	1.1.18 12:00	0.8545	14.8642				0
3	1.1.18 13:00	0.8398	14.8642				0	Criteria
4	1.1.18 14:00	0.8245	14.8642				0	Wave Height Max	1	m
5	1.1.18 15:00	0.8132	14.8642				0	Wave Period Max	12	s
6	1.1.18 16:00	0.8058	14.8642				0	Duration of Window	6	hours
7	1.1.18 17:00	0.7979	13.5129				0
8	1.1.18 18:00	0.7913	13.5129				0	Number of Available Windows	2
9	1.1.18 19:00	0.7574	13.5129				0
10	1.1.18 20:00	0.7874	13.5129				0		Column	G
11	1.1.18 21:00	0.7864	13.5129				0		Start Row	2
12	1.1.18 22:00	0.7803	12.2845				0
13	1.1.18 23:00	0.7698	12.2845				0		Ending Row	75
14	1.2.18 0:00	0.7538	12.2845				0		Range	G2:G75
15	1.2.18 1:00	0.7377	12.2845				0		Search Value	0111111
Sheet3

Cell Formulas
Range	Formula
I8	I8	=(LEN(CONCAT(INDIRECT(J14)))-LEN(SUBSTITUTE(CONCAT(INDIRECT(J14)),J15,"")))/(I6+1)
G2:G15	G2	=IF(AND(C2<$I$5,B2<$I$4),1,0)
J13	J13	=LOOKUP(2,1/(INDIRECT(J10&":"&J10)<>""),ROW(INDIRECT(J10&":"&J10)))
J14	J14	=J10&J11&":"&J10&J13
J15	J15	="0" & REPT("1",I6)

Hope it helps!

Thank you so much for the thorough explanation and solution to my problem. If I can get it to work this is exactly what I'm looking for, thank you!!

Unfortunately, I continuously get the formula error when trying the formulas you provided. Are there any settings I should be concerned about? I have tried entering with CSE and enter only as well, however, I can't seem to get around the formula error.

I am relatively new to excel, so thank you for bearing with me.

 

[Peter_SSs]

Unfortunately, I continuously get the formula error when trying the formulas you provided. Are there any settings I should be concerned about?

I have not really looked at your problem or the formulas suggested, but a common issue with what you are describing is that your language version of Excel might normally use semicolons to separate functions arguments whereas the suggestion above use commas.

So, for example, instead of
=IF(AND(C2<$I$5,B2<$I$4),1,0)
you might need
=IF(AND(C2<$I$5;B2<$I$4);1;0)

 

[FWE_EXCEL]

I have not really looked at your problem or the formulas suggested, but a common issue with what you are describing is that your language version of Excel might normally use semicolons to separate functions arguments whereas the suggestion above use commas.

So, for example, instead of
=IF(AND(C2<$I$5,B2<$I$4),1,0)
you might need
=IF(AND(C2<$I$5;B2<$I$4);1;0)

Hi Peter, and thank you for the reply. This was exactly what was causing the errors. Thank you!

I have marked mackc557's reply as the solution. I wish both of you ha nice weekend! :D

 

[Peter_SSs]

Hi Peter, and thank you for the reply. This was exactly what was causing the errors. Thank you!

You're welcome. Glad it helped.

I have marked mackc557's reply as the solution.

That is the correct thing to do.