Excel - Find how many cells are in a range

1
votes

For each cell in the column I need to get the number of cells in the column that are in the range of the value of the cell and the value + 10. I tried COUNtIF

Let's say this is the column:

11  -> how many cells between 11 and 20
23  -> how many cells between 34 and 43
17  -> how many cells between 17 and 27
13
26

Tried this but it only displays zeros:

=COUNTIFS(B:B; ">=B2"; B:B; "<B2+10")
1
excel

1 Answers

1
votes

You are grouping the string concatenation wrong.

=COUNTIFS(B:B; ">="&B2; B:B; "<"&B2+10)

A number literal (e.g. 10) can be included or not included in the quoted string with the operator but a cell address cannot be included in the string and must be concatenated together with the quoted operator.

..., ">=11")   'this is OK
..., ">="&11)  'this is OK
..., ">="&B2)  'this is OK
..., ">=B2")   'this is NOT OK