Excel 2007 : Counting with Criteria Problem.

[Kinf00]

Greetings,

I have what I believe is quite a challenging problem to solve.
I have a list of times in one column, and a list of aircraft types in another column.

What I need is a way to search an array of times, and return a number of how many of each aircraft type is listed in that time array.

Example: how many 763 aircraft are listed between 14:00 and 14:59

I've attached a worksheet of data as an example of the data I'm dealing with. I hope someone can help with this. The actual data set will be significantly larger, but this should do for an example.

Thanks in advance.
Chris

[JBeaucaire]

Hi Kinf00.. Welcome to the forum. I'll take a look at your problem.

In the meantime, please read through the Forum Rules so you can use and follow them effectively.

You'll need to EDIT your post above and GO ADVANCED, then change the title to something appropriate for this topic... perhaps "COUNTING WITH CRITERIA".


In the future, perhaps write out your entire question first, then go back and give it a title, it's usually much better when you do that.

[Kinf00]

Thanks for the heads up, I will do that, I was just in a bit of a rush, as I'm trying to get this sheet working by tomorrow. I do appreciate the help.

[JBeaucaire]

Many ways to slay this dragon. One way:

H2 = 14:00 (start time)
H3 = 14:59 (end time)

H4 =COUNTIF(A:A,">"&H2) - COUNTIF(A:A,">"&H3) (formatted as General)

[Kinf00]

That only counts everything between 14:00 and 14:59, I need it to only count the number of 763's in that time window

[JBeaucaire]

H2 = 14:00 (start time)
H3 = 14:59 (end time)
H4 = 763 (Type)

H6 =SUMPRODUCT(--($A$2:$A100>=H2), --($A$2:$A100<=H3), --($D$2:$D$100=H4))

[Kinf00]

Thats the solution I was looking for. Thanks for your help