count the number of events in an interval

**invictus** · 06-11-2012, 05:54 PM

Hi All,

After trying a chain of unsuccessful, CountIF(AND()) type nested formulas, decided to ask here as couldn't get my head around.

I have two excel worksheets WS1 and WS2 in a workbook.

On WS1, I have a list of intervals with 3 columns, A:interval ID, B:intervalBegin C:intervalEnd. Each row is an interval.
On WS2, I have a list of events with 2 columns; A:eventID B:eventTime. each row is an event
All times are in dd/mm/yyyy hh:mm:ss format.

I want to count how many events from WS2 occurred for each interval of WS1.

I will really really appreciate a quick response... Thanks in advance.

**oeldere** · 06-11-2012, 06:01 PM

In that case you better added an example of your workbook (without confidential information).

**TMS** · 06-11-2012, 06:06 PM

With Excel 2010, you should be able to use COUNTIFS.

Regards, TMS

**tigeravatar** · 06-11-2012, 06:07 PM

invictus,

I think what you're looking for is the Countifs() function (Excel 2007 and higher only)
Attached is an example workbook based on the criteria described. In sheet 'WS1' cell D2 and copied down is this formula:

Please Login or Register  to view this content.

**invictus** · 06-11-2012, 06:17 PM

That's brilliant tigeravatar! Thanks a million and for the example!

This CountIFs will be pretty useful since I was failing these type of multiple comparison.
Thanks for the quick responses folks!