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

nuhurizon

New Member
Joined
May 29, 2015
Messages
10
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?

uc


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

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

nuhurizon

New Member
Joined
May 29, 2015
Messages
10
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203
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

New Member
Joined
May 29, 2015
Messages
10

ADVERTISEMENT

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
 

nuhurizon

New Member
Joined
May 29, 2015
Messages
10

ADVERTISEMENT

Correct. Time isn't a factor.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203
Correct. Time isn't a factor.

Row\Col
A​
B​
C​
D​
E​
F​
G​
H​
1​
DATETIMECALL FROM > TORESULT
2​
7/1/2015​
2:29 PM​
Bob > SalMissed CallBobSal
2​
3​
7/1/2015​
2:40 PM​
Sal > BobCall AnsweredSalBob
1​
4​
7/2/2015​
3:09 PM​
Bob > SalMissed Call
5​
7/4/2015​
3:17 PM​
Bob > SalMissed Call
6​
7/5/2015​
3:28 PM​
Sal > BobMissed Call
7​
7/5/2015​
7:43 PM​
Bob > SalCall Answered
8​
7/6/2015​
3:50 PM​
Sal > BobMissed Call
9​
7/6/2015​
4:10 PM​
Bob > SalMissed Call
10​
7/6/2015​
6:00 PM​
Sal > BobCall Answered
11​
7/7/2015​
8:00 AM​
Bob > SalMissed Call
12​
7/7/2015​
12:00 PM​
Sal > BobCall Answered
13​
7/7/2015​
7:00 PM​
Bob > SalMissed 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))
 

nuhurizon

New Member
Joined
May 29, 2015
Messages
10
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 CallBobSal3
3​
7/1/2015​
Sal > Bob​
Call AnsweredSalBob0
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 CallBobSal3
3​
7/1/2015​
Sal​
Bob​
Call AnsweredSalBob0
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>
 

Watch MrExcel Video

Forum statistics

Threads
1,129,750
Messages
5,638,129
Members
417,010
Latest member
jnuss03

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

Which adblocker are you using?

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
Top