Help with multiple dates and values to count occurrences

Hello,
I want to count the occurrences that the river crests over a certain height in feet. For example, I want to count a 41ft crest as a single over 40ft river level occurrence. I do not know how to count this crest as a single event. If the river goes over 40ft on 04/17/2022, crests at 41ft on 04/18/2022 and then goes below 40ft on 04/20/2022. This example occurrence over 40ft should count as an above 40ft occurrence and also count as any occurrence over 38ft, 36, 34, etc. I have 15 years of data with levels each hour. Is it possible to count these river levels by occurrence? This is for my own use to track river levels on the farm. I attached a truncated spreadsheet.
Thank you.

Your explanation is incomplete...

Time 1 37
Time 2 41
Time 3 37

Does Time 2 count as >=38 AND as >=40... or just as >=40????

If it counts as both this MIGHT be what you want:

=SUMPRODUCT(($E$3:$E$48>=40)*($E$4:$E$49<40))

and similar...

Try this then:

=SUMPRODUCT(($E$3:$E$48>=I$2)*($E$4:$E$49<I$2))

however, there is a further complication.. look at the yellow cells. How many >40 should there be?

OK.

You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please select "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.

This seems to work for dates that fall between April 1 and October 31, inclusive, on the Crests sheet:

Formula:

=SUMPRODUCT(($C$1:$C$44>=H$2)*($C$3:$C$46<H$2)*($C3:$C46<>0)*(MONTH($E2:$E45)>=4)*(MONTH($E2:$E45)<=10))

If you have hourly or daily data then there may be a way to employ a helper column to find dates between April 15 and October 15 of the years included, however I would need data from April of one year through October of the next to test/demonstrate.
Let us know if you have any questions.

Thank you. I think I can work with the whole months rather than a specific date, but I cannot get the month function to discriminate the months. Any idea what is wrong?
This formula,
=SUMPRODUCT(($E$3:$E$502853>=I$2)*($E$4:$E$502854<I$2))
Gives the same output as this formula,
=SUMPRODUCT(($E$3:$E$502853>=I$2)*($E$4:$E$502854<I$2))*($E3:$E502853<>0)*(MONTH($C3:$C502853)>=4)*(MONTH($C3:$C502853)<=10)

Also, when I click download on the file you posted, it takes me to the login screen even when I am logged in. It will not allow me to download the file. Any idea why?

If you are sure that some of the flooding is occurring outside of the dates between 1 April and 31 October then I would check to make sure the values in column C are actually dates by widening the column to see that the values right align.
If that doesn't help, then please upload about 100k rows of the data so that we can troubleshoot.
I just downloaded the file without problem, so I don't know why the site isn't allowing you to download.

It looks ok to me, but I must have something wrong. I attached my file truncated to 30k rows. In this spreadsheet, the over 36ft occurrences in December were my "test" as outside the date range.

There was an out of place end parenthesis in the month formula (I should have spotted that from post #10).
Try: =SUMPRODUCT(($E$3:$E$502853>=I$2)*($E$4:$E$502854<I$2)*($E3:$E502853<>0)*(MONTH($C3:$C502853)>=4)*(MONTH($C3:$C502853)<=10))
Let us know if you have any questions.

Thie enables you to select individual start/end dates:

=IF(SUMPRODUCT((E3:E30000>I7)*(TEXT(C3:C30000,"mmdd")+0>=TEXT(I8,"mmdd")+0)*(TEXT(C3:C30000,"mmdd")+0<=TEXT(I9,"mmdd")+0))>0,"Yes","No")

I've seen this behaviour before. It's an intermittent EF "feature".

It's a pain in the @ss... no idea what causes it.

If you open the sheet you posted..

I7 desired crest height
I8 desired start date dd/mm/yyyy
I9 desired end date.

Then copy/paste formula.

The years are excluded from the calculation. Only dd and mm are considered.

Thank you, Glenn. Can I change the yes- no output to a number of times of occurrence?

Back to my entire spreadsheet, I calculated levels by month and for a reason I cannot figure out, for one particular month I get an occurrence counted at 50, but not for 48 or 49. The data has 48 and 49 occurrences, but the formula does not count them. And for another month, 48 is counted but 44-47 are not counted. Any ideas why?

The formula is set up to count a crest as being a rise to/above a level and then a fall below the level during a time period. Could it be that while the water rose from below 48 to 50 during the month that it only fell to 49 during that month so that 49 and 48 would not be counted as a crest?