Counting instances when multiple conditions are met on the same date?

[nuhurizon]

I apologize for the confusing title. I need your help.

To calculate how many times Sal did not return Bob's call the same day within this date range, which formula(s) could be used to accomplish this?

I've searched extensively and have tried COUNTIFS, SUMIFS, Multiple Criterias for INDEX and MATCH and can't find any help on how to do this.

Thank you!

 

[Aladin Akyurek]

What is the result you want to see?

 

[nuhurizon]

The result should be 2 (on 7/2/15 & 7/4/15 Sal did not call back Bob on the same date that he had missed Bob's call)

I need a formula that compares when the conditions Missed Call + Call Answered in Column D do not occur on the same date in Column A.

 

[Aladin Akyurek]

The result should be 2 (on 7/2/15 & 7/4/15 Sal did not call back Bob on the same date that he had missed Bob's call)

I need a formula that compares when the conditions Missed Call + Call Answered in Column D do not occur on the same date in Column A.

Would you post directly on this board the sample of the image of your initial post, using an appropriate method: Attachments or https://app.box.com/s/soezox25h3w0q5s4rcyl?

 

[nuhurizon]

Sorry for the delay. Thank you for your patience.

Row\Col	A​	B​	C​	D​
1​	DATE​	TIME​	CALL FROM > TO​	RESULT
2​	7/1/2015​	2:29 PM​	Bob > Sal​	Missed Call
3​	7/1/2015​	2:40 PM​	Sal > Bob​	Call Answered
4​	7/2/2015​	3:09 PM​	Bob > Sal​	Missed Call
5​	7/4/2015​	3:17 PM​	Bob > Sal​	Missed Call
6​	7/5/2015​	3:28 PM​	Sal > Bob​	Missed Call
7​	7/5/2105​	7:43 PM​	Bob > Sal​	Call Answered
8​	7/6/2015​	3:50 PM​	Sal > Bob​	Missed Call
9​	7/6/2015​	4:10 PM​	Bob > Sal​	Missed Call
10​	7/6/2015​	6:00 PM​	Sal > Bob​	Call Answered
11​	7/7/2015​	8:00 AM​	Bob > Sal​	Missed Call
12​	7/7/2015​	12:00 PM​	Sal > Bob​	Call Answered
13​	7/7/2015​	7:00 PM​	Bob > Sal​	Missed Call

 

[Aladin Akyurek]

I have the impression that we can safely ignore TIME values. Is this right?

 

[nuhurizon]

Correct. Time isn't a factor.

 

[Aladin Akyurek]

Correct. Time isn't a factor.

Row\Col	A​	B​	C​	D​	E​	F​	G​	H​
1​	DATE	TIME	CALL FROM > TO	RESULT
2​	7/1/2015​	2:29 PM​	Bob > Sal	Missed Call		Bob	Sal	2​
3​	7/1/2015​	2:40 PM​	Sal > Bob	Call Answered		Sal	Bob	1​
4​	7/2/2015​	3:09 PM​	Bob > Sal	Missed Call
5​	7/4/2015​	3:17 PM​	Bob > Sal	Missed Call
6​	7/5/2015​	3:28 PM​	Sal > Bob	Missed Call
7​	7/5/2015​	7:43 PM​	Bob > Sal	Call Answered
8​	7/6/2015​	3:50 PM​	Sal > Bob	Missed Call
9​	7/6/2015​	4:10 PM​	Bob > Sal	Missed Call
10​	7/6/2015​	6:00 PM​	Sal > Bob	Call Answered
11​	7/7/2015​	8:00 AM​	Bob > Sal	Missed Call
12​	7/7/2015​	12:00 PM​	Sal > Bob	Call Answered
13​	7/7/2015​	7:00 PM​	Bob > Sal	Missed Call

H2, control+shift+enter, not just enter, and copy down:

=SUM(IF(ISNA(MATCH(IF(FREQUENCY(IF(ISNUMBER(SEARCH("|"&F2,"|"&$C$2:$C$13)),
    IF($D$2:$D$13="Missed Call",$A$2:$A$13)),$A$2:$A$13),$A$2:$A$13,"#"),
    IF(ISNUMBER(SEARCH("|"&G2,"|"&$C$2:$C$13)),
    IF($D$2:$D$13="Call Answered",$A$2:$A$13,"#"),"#"),0)),1))

 

[nuhurizon]

THANK YOU FOR THE QUICK RESPONSE!

I think it is almost there... I changed some values to test some of the variables in the formula:

Row\Col	A​	B​	C​	D​	E​	F​	G​
1​	DATE​	CALL TO​	RESULT
2​	7/1/2015​	Sal > Bob​	Missed Call		Bob	Sal	3
3​	7/1/2015​	Sal > Bob​	Call Answered		Sal	Bob	0
4​	7/2/2015​	Sal > Bob​	Missed Call
5​	7/4/2015​	Bob > Sal​	Missed Call
6​	7/5/2015​	Sal > Bob​	Missed Call
7​	7/5/2105​	Bob > Sal​	Call Answered
8​	7/6/2015​	Sal > Bob​	Missed Call
9​	7/6/2015​	Bob > Sal​	Missed Call
10​	7/6/2015​	Sal > Bob​	Missed Call
11​	7/7/2015​	Bob > Sal​	Missed Call
12​	7/7/2015​	Sal > Bob​	Missed Call
13​	7/7/2015​	Bob > Sal​	Call Answered

<tbody>
</tbody>

The results should be:
Bob's Calls was not returned by Sal: 1 (7/4/2015)
Sal's Call was not returned by Bob: 2 (7/2/2015 & 7/6/2015)

How could the formula be tweaked for it to see who called first on a date when all missed calls occur on the same day?

I hope that makes sense. Thank you again for your help!

Also it if is helpful here is the same table with Column B separated into two columns:

Row\Col	A​	B​	C​	D​	E​	F​	G​	H​
1​	DATE​	CALL FROM​	CALL TO​	RESULT
2​	7/1/2015​	Sal​	Bob​	Missed Call		Bob	Sal	3
3​	7/1/2015​	Sal​	Bob​	Call Answered		Sal	Bob	0
4​	7/2/2015​	Sal​	Bob​	Missed Call
5​	7/4/2015​	Bob​	Sal​	Missed Call
6​	7/5/2015​	Sal​	Bob​	Missed Call
7​	7/5/2105​	Bob​	Sal​	Call Answered
8​	7/6/2015​	Sal​	Bob​	Missed Call
9​	7/6/2015​	Bob​	Sal​	Missed Call
10​	7/6/2015​	Sal​	Bob​	Missed Call
11​	7/7/2015​	Bob​	Sal​	Missed Call
12​	7/7/2015​	Sal​	Bob​	Missed Call
13​	7/7/2015​	Bob​	Sal​	Call Answered

<tbody>
</tbody>