# formula to count how many ranges of values a number fits between

#### qwerty2k1

##### New Member
I've been searching around for a way to do this but have had no luck so far.

Say I have a bunch of ranges of times. The start time is in Column A, and the end time is in Column B. These ranges overlap, often, so I might have:
A B
0 6
4 6
7 11
16 18

I have another column, column C, and I want to see how many of the ranges above the numbers in column C fit into.

So, if I have:
C
1
5
8
10
15

Then I want my outcome to be
D
1
2
1
1
0

Thank you for any help!

### Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Try using COUNTIFS in column D, e.g. in D2

=COUNTIFS(A\$2:A\$10,"<="&C2,B\$2:B\$10,">="&C2)

Welcome to the board.
If you have XL2007+ Try

=COUNTIFS(A\$1:A\$4,"<="&C1,B\$1:B\$4,">="&C1)

Thank you! I hadn't thought that would keep the adjacent cells in columns A and B paired, but it seems to accomplish that. Is that the work of the \$? I'm somewhat familiar with \$ in excel but I hadn't thought it would work that way here.

Replies
7
Views
813
Replies
3
Views
238
Replies
3
Views
298
Replies
3
Views
317
Replies
2
Views
397

1,196,328
Messages
6,014,674
Members
441,835
Latest member
rthomas268

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back