COUNTIFS Data Range Criterion

[alext23]

Hi everyone!

I am currently working on a simple table matrix in which I want to count the occurrences that match certain criteria. I have data points for wave height and period and want to create its power matrix, a.k.a. count how many times there is a data entry which has a height between 0.1 and 0.3 [m] AND a corresponding period between 2.0 and 2.5 [s]. I want to do this for a range of height between 0.1 m and 6.5 m (0.2 m increments) and a range of periods between 2.0 s and 21 s (0.5 s increments).

I was trying to do this using =COUNTIFS through something like this for a case of height between 0.76 and 0.77 and a period between 4 and 5 seconds:

=COUNTIFS($N$47:$N$63;(AND(">=0.76";"<0.77"));$O$47:$O$63;(AND(">=4";"<5")))

But it always gives 0 as a result. Ideally, I wouldn't have to type in the range restriction values each time but just refer to the cells that are defined in the table. Here is the link to the file (with a " " between .com), and below the main table is my 'playground' for trying to figure it out on a much smaller database.

uoe-my.sharepoint .com/:x:/g/personal/s1969090_ed_ac_uk/ESA8mk1gwpxNnXR7buAM7VwBJXI4OW1QWYOEuUY3V9PnFA?e=Zwfb2o

All the help would be very much appreciated!

[Glenn Kennedy]

The correct syntax is:

=COUNTIFS($B:$B,">="&$G7,$B:$B,"<"&$G7+0.2,$C:$C,">="&H$6,$C:$C,"<"&H$6+0.5)

copied across and down.

[alext23]

Fantastic Glenn! Thank you very much!

[Glenn Kennedy]

This is a public forum. It is there to help all comers. Deleting your own post after you received a satisfactory reply is a little mean-spirited.

I have restored it.

Please do not do that again.