How to calculate frequency

[jb]

Hi experts,

I have an excel sheet in which column A and B represents range of x. Column C and D represents range of y. For example A3 is 72 and B3 is 73. Means it is a range 72-73.
Same way C3 is 100 and D3 is 109. Means it is a range 100-109.

Data is given in column I and J. I want to calculate frequency in column F.

F3 will contain a number of rows which satisfy the criteria. Means all rows whose X value is >= A3 and <= B3 as well as whose Y value is >= C3 and <= D3.

I have given only 4 rows as data. I have almost 300 + rows.

e.g. first data row 72 and 115 must be counted in F4 cell.

Help required. I tried countifs. But not working.

I am attaching sample sheet here.

Thanks in advance.

JB

 

[Ajesh]

Hi Jb,

Please see your file enclosed. I have done it for Y data with help of a helper column.

Thanks

 

[NARAYANK991]

Hi ,

I am somewhat confused by what your requirement is ; however , your COUNTIFS formula should be as follows :

=COUNTIFS($A$3:$A$18,"<=" &I3,$B$3:$B$18,">=" & I3,$C$3:$C$18,"<=" & J3,$D$3:$D$18,">=" & J3)

Since I3 and J3 are cell references , they cannot be included within the quotes ; they should be outside the quotes.

Narayan