Search for Condition, Text based, Date Range, Occurrences

[NickNameGoesHere]

Hello,


I am trying to count the number of occurrences
a text based instance occurs
within a date range
based upon the text found within the Activity.



So for example, I have a Sales Report Sheet with the following
activity per rep.


Date Rep Activity Title
Jan 1 Sally Sue Voice Mail
Jan 3 Bob Greene Voice Mail
Jan 3 Sally Sue Talked to George
Jan 10 Bob Greene Talked to Kim
Feb 1 Sally sue Voice Mail
Feb 10 Sally Sue Talked to Steve


I would like to run a formula that will tell me
all occurrences of "Bob Greene",
Left Voice mail, in January.

all occurrences of "Bob Greene",
Left Voice mail, in February

AND

all occurrences of "Bob Greene",
and "Bob Greene" with "talked to", in January.

all occurrences of "Bob Greene",
Left Voice mail, in February

I think I need to run a "conditional if", however have no idea how to
do text based searches.

Can anyone help.

Thanks!

 

[PCLIVE]

=SUMPRODUCT(--(B2:B10="Bob Greene"),--(C2:C10="Voice
Mail"),--(TEXT(A2:A10,"mmm")="Jan"))

or if you have your criteria in a cell.

E1= Rep Name
F1= Voice Mail
G1= Month (in "mmm" format...ex. Jan, Feb, Mar, etc.)

=SUMPRODUCT(--(B2:B10=$E$1),--(C2:C10=$F$1),--(TEXT(A2:A10,"mmm")=$G$1))


For "Talked to", if those are the only items that there could be, then you
could use,

=SUMPRODUCT(--(B2:B10="Bob Greene"),--(C2:C10<>"Voice
Mail"),--(TEXT(A2:A10,"mmm")="Jan"))
or
=SUMPRODUCT(--(B2:B10=$E$1),--(C2:C10<>$F$1),--(TEXT(A2:A10,"mmm")=$G$1))


HTH,
Paul