count how many cells have values between 2 defined values AND neighboring cell value is...

[mlelston]

So I have a bit of a puzzle I've been working on, and I've been bashing my head against the wall trying to get it to work properly. I'm hoping someone here can help me a little.

Screen Shot 2015-09-28 at 12.23.06 PM.png

I have a slew of 3-second windows (indicated in columns G and H), during which I'm have found how many times (listed column F) fit within this 3 second window. This is the first step I've needed to complete, and I've gotten it (in column I):
=COUNTIF(F:F,">="&G3)-COUNTIF(F:F,">"&H3)

I am trying to take this a step further. I want to see how many of those times listed in column F that occur in the 3 second window have a particular categorical value (indicated in column E). The category listed in column E indicates the type of behavior found at the time listed in column F. Ultimately, I am looking to find how many sensitive behaviors occur within the 3 second windows, and how many redirective behaviors occur within the 3 second windows independently.

I have been trying to implement a way to indicate the cell to the left of column F in such a way that couples together the column E and F values on a particular row, and yet checks ALL rows. I've not succeeded so far.
I've tried using indirect and address functions, but either I'm going about it the wrong way or I'm messing up the code. Can anyone help me figure out what I'm doing wrong?

Here is my latest attempt:

=COUNTIF(F:F,AND(F:F>=&G3,indirect(address(1:1000,6,2,,),0,-1)="sensitive"))- COUNTIF(F:F,AND(F:F>=&H3,indirect(address(1:1000,6,2,,),0,-1)="sensitive"))

Any help is appreciated!

[Pepe Le Mokko]

Perhaps post a sample sheet. It's easier to work with

[MrShorty]

I would probably use the =COUNTIFS() function rather than a combination of COUNTIF() functions: https://support.office.com/en-us/art...c-aa8c2a866842

[mlelston]

sample data.xlsx

Okay, here is a sample of some of the data. There is one other category in this slightly longer list (initiative), but that shouldn't change the nature of the problem. Thank you so much for looking into this!

[mlelston]

that is a good suggestion. I'll play around with that a little. I tried it before, but I've since come up with a few other methods that I haven't tried with that particular function

[Jonathan68]

what do you think about this. in cell I4 you could use the following formula

=COUNTIFS(E$4:E$48,I$3,F$4:F$48,">"&G4)-COUNTIFS(E$4:E$48,I$3,F$4:F$48,">"&H4)

From what you said, it sounded like you would like to be able to filter the results by the categories in column E but would like the results to show up in a single column (I) as opposed to having 3 separate columns displaying these results individually.

In the attachment I've set it up so that you choose which category you want in cell I3 and the results change accordingly. You can either manually enter the category in cell I3 or create a validation list as I have done so that you can choose the category from a drop down box

Sample Data.xlsx

If you want the formula to only reference the category listed in the same row then use

=COUNTIFS(E$4:E$48,E4,F$4:F$48,">"&G4)-COUNTIFS(E$4:E$48,E4,F$4:F$48,">"&H4)

[Jonmo1]

=COUNTIFS(E$4:E$48,I$3,F$4:F$48,">"&G4)-COUNTIFS(E$4:E$48,I$3,F$4:F$48,">"&H4)

I would put that into a single countifs
=COUNTIFS(E$4:E$48,E$4,F$4:F$48,">"&G4,F$4:F$48,"<="&H4)

[Jonathan68]

Jonmo1, you're right that is much better

here's the sample sheet using Jonmo1's formula change

[mlelston]

Thank you so much! I just checked over one full file and every cell was correct. You have saved me hours of searching through these files manually!