1

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

enter image description here

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

enter image description here

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!

Dustin Burns
  • 307
  • 4
  • 13
  • If KC was success on 1/1/17 and 1/14/17 and BW was success on 1/1/17 why is your expected result 1 for 1/1/17-1/31/17? Seems it should be 2. –  Apr 26 '18 at 02:03

2 Answers2

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.

enter image description here

  • I've revised your formula to respond to filtering attempts using in-cell references. These criteria are "Grant" and "Case Manager" - I'll post the formula in a comment below. If I want to allow any grant or any case manager, I try to fill the formula with the * sign, which works in other formulas, but not in this one. Any ideas how to modify the formula? – Dustin Burns Aug 09 '18 at 16:07
  • =SUMPRODUCT(((Encounters[Date]>=$J9)*(Encounters[Date]<=$I9)*(Encounters[CM svc]="S")*(Encounters[Grant]=$L$3)*(Encounters[Case Manager]=$P$3))/(COUNTIFS(Encounters[Name], Encounters[Name], Encounters[Date], ">="&$J9, Encounters[Date], "<="&$I9, Encounters[CM svc], "S", Encounters[Grant], $L$3, Encounters[Case Manager], $P$3)+(Encounters[Date]<$J9)+(Encounters[Date]>$I9)+(Encounters[CM svc]<>"S")+(Encounters[Grant]<>$L$3)+(Encounters[Case Manager]<>$P$3))) – Dustin Burns Aug 09 '18 at 16:07
0

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.

Oskar
  • 108
  • 9