Countif with "between" criterium

[carmenlisa]

I would like to to do oountif that counts instances where a value is between the values in two other columns. In my case, I am using time, but I suspect the format of the data is not terribly relevant.

An example of the data would be the following in columns A and B beginning in row 1 (these are times people were present at an event):

12:05 12:15
11:59 12:14
12:10 12:13

What I want to count is how many people were there within 3 minute intervals.
So, how many people were there at some point (but not necessarily for the entire 3 minute period) between:
12:00 and 12:02
12:03 and 12:05
12:06 and 12:08
12:09 and 12:11
12:12 and 12:14
12:15 and 12:17

I know that I could add new columns, use IF statements to determine Y or N if a time were in various intervals and then ountif the Y's and N's. But this could quickly get out of hand with either complicated nested IF statements or an excessive number of columns testing for each minute one by one.

Or I could move the data into Access and use BETWEEN queries to seimply it somewhat.

But it seems that there should be some way to do the equivalent of a betwen in an CountIf. I just am not very experienced in using Countif and SumIf.

I would appreciate anyone telling me how to do this.

 

[Lilliabeth]

If the 3-min intervals are in cols F and G (begin times in F, and end in G), then:
=COUNTIFS($A$1:$A$30,"<="&G1,$B$1:$B$30,">="&F1)

The COUNTIFS is relatively new; Microsoft added it in version 2007, so this won't work if you have a very old version of Excel.

--Lilliabeth

 

[SkipVought]

Hi,

What version of Excel?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[carmenlisa]

Thank you for the help. I need to go read up on Countifs.

 

[SkipVought]

COUNTIFS() ONLY in Excel 2007+, which is why I asked for your VERSION?



Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]