excel - Counting number of times value exceeded for a range of dates -

excel - Counting number of times value exceeded for a range of dates -

i have list of wave heights measured hourly every day in year. want have excel count if wave height exceeds value (say, 6m in height) 1 time given day - is, if info says wave height on 6m 3 times on 1 day, count single occurrence.

date wave height 1/1/98 1.99 1/1/98 2.16 1/1/98 2.21 1/1/98 6.50 1/1/98 6.71 1/1/98 2.27 1/1/98 2.31

in above info set, i'd date 1/1/98 have "1" count since exceeds 6m @ to the lowest degree once, , i'd go on way downwards every date. i've been trying utilize countifs equations, haven't been able quite want. lastly 1 tried: =countifs(h2:h8500,">=6",g2:g8500,"=$g2") h wave height column , g date column. can help me on this?

i work this:

first assign limit (6m) value cell general constant, since may alter in future. way can alter value wherever beingness used (update formulas). in our case constant lies on cell b1 (thus static reference $b$1 in formulas).

then layout info in illustration sheet set up:

there 24 columns, 1 every measurement in single day, since taken hourly. calculate whether there single limit nail throughout day (once in row) using next formula (example date 1/1/1998 on row 4):

=if(countif(o4:y4; ">" & $b$1) >= 1; true; false)

we finish sequence every date (row) in our datasheet.

at bottom include cell value period hits counts number of "true" values this:

=countif(aa4:aa13; true)

i broke downwards calculation in 2-steps since holding huge look multiple countifs ranges associated, not viable solution in such cases.

i hope interpreted correctly requirements.

excel date count conditional-statements