COUNTIFS Data Range Criterion

1. COUNTIFS Data Range Criterion

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!

2. Re: COUNTIFS Data Range Criterion

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.

3. Re: COUNTIFS Data Range Criterion

Fantastic Glenn! Thank you very much!

4. Re: COUNTIFS Data Range Criterion

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.

There are currently 1 users browsing this thread. (0 members and 1 guests)