# Thread: IF + OR formula for range Thanks: 0 Likes: 0

1. ## IF + OR formula for range

Hello,

If I have a list of values in the form (minutes:seconds)-
3:51
2:01
4:01
10:31
0:39

What formula allow me to say IF value is between 0-5:00, return '0-5:00', OR IF value is between 5:01-10:00, return 5:01-10:00, etc. I ultimately would like a count of the number of values within each range.

Thank you

2. ## Re: IF + OR formula for range

Hi, if you have to count each group, below can work for you directly:

ABCDE
1TimeBins
23:510
32:010:00 - 5:005:005
44:015:00 - 10:0010:000
510:3110:00 - 15:0015:002
60:3915:00 - 20:0020:001
715:0020:00 - 0:001
821:00
90:41
1016:00

Sheet6

Array Formulas
CellFormula
E3:E7{=FREQUENCY(A2:A10,D3:D6)}
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

Pleae do remember to select E3:E7 before entering Ctrl+Shift+Enter

3. ## Re: IF + OR formula for range

Try a regular formula

Actually in excel you must capture hours:minutes:seconds (hh:mm:ss) as shown below:

 A B C D E 1 minutes:seconds From To Result 2 00:03:51 00:00:00 00:05:00 4 3 00:02:01 00:05:01 00:10:00 0 4 00:04:01 00:10:01 00:15:00 2 5 00:10:31 00:15:01 00:20:00 3 6 00:00:39 00:20:01 00:25:00 1 7 00:12:05 00:25:01 00:30:00 0 8 00:17:05 00:30:01 00:35:00 0 9 00:18:10 10 00:19:15 11 00:20:20

 Cell Formula E2 =COUNTIFS(\$A\$2:\$A\$11,">="&C2,\$A\$2:\$A\$11,"<="&D2)

----------------------
But you can change the format of the cell to show only minutes and seconds (mm:ss)

Example:
 A B C D E 1 minutes:seconds From To Result 2 03:51 00:00 05:00 4 3 02:01 05:01 10:00 0 4 04:01 10:01 15:00 2 5 10:31 15:01 20:00 3 6 00:39 20:01 25:00 1 7 12:05 25:01 30:00 0 8 17:05 30:01 35:00 0 9 18:10 10 19:15 11 20:20

 Cell Formula E2 =COUNTIFS(\$A\$2:\$A\$11,">="&C2,\$A\$2:\$A\$11,"<="&D2)

4. ## Re: IF + OR formula for range

Thank you both

6. ## Re: IF + OR formula for range

Your'e welcome, thanks for the feedback.