Counting number of distinct people successfully co

Question

Good Afternoon,

I have a large data table which contains accounts of attempts to contact clients. Each entry is marked with:

a. The name of the client b. The date contact was attempted c. Whether the contact attempt was successful (S) or not (U)

Here's an example of the data, with confidential names replaced

I need to calculate the number of distinct people successfully contacted each month, and display it in a table like this:

Note that I don't want to count the number of time's they've been successfully contacted, just if they have at least been contacted once.

I've tried the following formula:

{=SUMPRODUCT(IF((EncounterDate<=DU4)*(EncounterDate>=DT4), 
1/COUNTIFS(EncounterDate, "<="&DU4, EncounterDate, ">="&DT4, 
EncLastFirstName, EncLastFirstName, CMService, "S"), 0))}

DU4 - Last Date of month
DT4 - First Date of month

I'm getting a Div/0 error with this formula. Any idea what I'm doing wrong, or what I should be doing instead?

Thanks, I appreciate it!

Answer 1

Maybe the problem is that you just check the date and then assume that there was at least one successful contact. Some people might have only unsuccessful attempts and so then you divide by 0.

Answer 2

You're getting a #DIV/0! because the denominator of your division can be zero and formulas 'short circuit' on the first unhandled error. If any row delivers a result of zero from,

COUNTIFS(EncounterDate, "<="&DU4, EncounterDate, ">="&DT4, EncLastFirstName, EncLastFirstName, CMService, "S")

... then you get a #DIV/0! with 1/COUNTIFS(...).

My solution is to remove the IF portion and collect each criteria into a numerator that resolves as 1 if all true and 0 if any false. Additionally, add the inverse of the criteria numerator to the denominator of the division operation; e.g. if (CMService="S") is in the numerator, then add (CMService<>"S") to the denominator. In these cases, the denominator will be non-zero and the numerator will be zero. Zero divided by anything is still zero (e.g. 0/100 == 0) so it doesn't matter what the denominator is and you will never get a #DIV/0! error.

If all conditions in the numerator and countifs are true, the numerator is 1 and nothing has been added to the denominator. If any conditions in the numerator and countifs are false, the numerator is 0 and something has been added to the denominator to ensure that no #DIV/0! will occur.

In DV4 as a standard formula without CSE,

             [<~~~~~~~~~~~~~~~~~ numerator portion ~~~~~~~~~~~~~~~~~>] / [<~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ denominator portion ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~>]+[<~~~~~~~~~~ denominator adjustment portion ~~~~~~~~~~>]
=sumproduct(((EncounterDate>=DT4)*(EncounterDate<=DU4)*(CMService="S"))/(countifs(EncLastFirstName, EncLastFirstName, EncounterDate, ">="&DT4, EncounterDate, "<="&DU4, CMService, "S")+(EncounterDate<DT4)+(EncounterDate>DU4)+(CMService<>"S")))

With this method of cancelling out any #DIV/0!'s with basic maths, you can increase or decrease the number of criteria as long as you keep the denominator adjustment to the inverse of the numerator.

BTW, the expected results in your question are wrong.