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

nuhurizon

New Member
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!

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
What is the result you want to see?

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.

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?

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

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

Correct. Time isn't a factor.

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:
Rich (BB code):
``````
=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))
``````

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>

Replies
14
Views
862
Replies
6
Views
424
Replies
0
Views
207
Replies
2
Views
269
Replies
6
Views
129

Threads
1,202,989
Messages
6,052,945
Members
444,620
Latest member
marialewis16

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back