Search code examples
excelconditional-statementsfrequency

Conditional Frequency for numerical data in excel

I have a list of categories for length of roads in Excel. While i can filter the result and get a frequency for each category alone. I want to know if there is a way to get a conditional frequency (with bin of 500) to get the frequency/number of roads in each 500m interval in each category (5-7m road, <5m and >7m road) using conditional statement? 

ID  LENGTH(m)   CATEGORY
49  779.057148  Public road, 5-7 m width
50  779.057148  Public road, 5-7 m width
109 40.939242   Public road, 5-7 m width
111 40.939242   Public road, 5-7 m width
381 123.495134  Public road, 5-7 m width
382 123.495134  Public road, 5-7 m width
423 66.196065   Public road, 5-7 m width
424 66.196065   Public road, 5-7 m width
523 1.559347    Public road, <5 m width
524 1.559347    Public road, <5 m width
963 409.818888  Public road, 5-7 m width
1178    220.234471  Public road, 5-7 m width
1307    25.574923   Public road, <5 m width
1321    88.248279   Public road, <5 m width
1335    116.828271  Public road, >7 m width
Solution

  • In F2 (per supplied image) as,

    =COUNTIFS($C:$C, F$1, $B:$B, ">"&(ROW(1:1)-1)*500, $B:$B, "<="&ROW(1:1)*500)

    Fill right and down.

    enter image description here