COUNTIFS with calculation

[dkubiak]

Given the table below, I want to COUNTIFS based on date, sales person, and IF column D - C is less than or equal to -15 minutes. I am trying to count how often the appointment starts at least 15 minutes early on a given date and sales person.

I want to create the entire thing in one function - i.e. not create a helper column in the table. Is this possible?

A	B	C	D
Date	Sales Person	Appointment Time	Actual Start Time

 

[Fluff]

Can you post some sample data along with expected results. Preferably using the XL2bb add-in to do so.

 

[Eric W]

How about:

Book1
	A	B	C	D	E	F	G	H	I
1	Date	Sales Person	Appointment Time	Actual Start Time			Sales Person	Date	# of 15 minute early appointments
2	1-Jan	Ann	12:00	12:05			Ann	1-Jan	3
3	1-Jan	Ann	11:00	10:30
4	1-Jan	Bob	10:00	10:15
5	1-Jan	Ann	9:00	8:44
6	1-Jan	Bob	8:00	8:03
7	1-Jan	Ann	1:00 PM	12:37 PM
8	1-Jan	Ann	2:00 PM	2:02 PM
9	2-Jan	Ann	12:00	12:00
10	2-Jan	Bob	12:00	12:00
11	2-Jan	Bob	1:00	1:01
Sheet13

Cell Formulas
Range	Formula
I2	I2	=SUMPRODUCT(--(A2:A11=H2),--(B2:B11=G2),--(C2:C11-D2:D11>=1/96))