Formula Help Needed to find Distinct Client Interactions

Copper Contributor

Hi!

 

I'm using Microsoft Excel 2016 on a PC.  I am creating a report that lists the number of specific types of interactions with clients by Team during a chosen time frame.  I have been able to accomplish this using COUNTIFS formulas.  This is the top section.  What I need to do now is to list the same data in the bottom section but this time counting the number of distinct clients that had that interaction.  I have searched and tried a number of different approaches, but I can't solve this :^(   Any help would be greatly appreciated!

 

Here is a link to an example file.  The first tab "Data" contains the data I'm summarizing.  The second sheet "Sheet1" contains the summary using formulas.   :  https://docs.google.com/spreadsheets/d/1VwOR_Y5qQTYDgK2Px3tdm0jKjARb-5Cj/edit?usp=share_link&ouid=10...

 

And here is an example of the formula I'm using on the top part.

=COUNTIFS(Data!$C:$C,Sheet1!$D$1,Data!$C:$C,Sheet1!$D$2,Data!$D:$D,Sheet1!$A6,Data!$E:$E,Sheet1!B$5)

 

Data!$C:$C has the time of the interaction

Sheet1!$D$1 concatenates ">=" with the desired Start Date

Sheet1!$D$2 concatenates "<=" with the desired End Date

Data!$D:$D has the Team Designation

Sheet1!$A6  Is the Row Label that contains the Team Designation for that row

Data!$E:$E has the type of interaction (in this example there are no blank rows but in the real situation there are a large number of blank rows).

Sheet1!B$5 is the Column Label that has the type of interaction in that Column

 

THANK YOU!

4 Replies

Hi @RobDevens,

 

if your COUNTIFS formula generally works ok, then you can try it like this for a distinct count:

 

{=SUM(1/COUNTIFS(Data!$C:$C,Sheet1!$D$1,Data!$C:$C,Sheet1!$D$2,Data!$D:$D,Sheet1!$A6,Data!$E:$E,Sheet1!B$5))}

 

Please note that you have to enter it as an array formula, That means, you must not enter the curly brackets manually, but use CTRL+Shift+Enter to close the formula.

 

Thanks for the suggestion! When I use this, though, I don't end up with the correct result. The formula produces 0.058823529 as the value.
It's very difficult to find out the reason for this without the file. So if possible, could you upload a sample file (without any sensitive data, of course), so it's easier for us to find a solution.
Thanks, Martin, I wasn't sure how to do that, but thought that attaching a link in my post to the file in Google docs would work. Sorry if that doesn't work. Here is the link again:
https://docs.google.com/spreadsheets/d/1VwOR_Y5qQTYDgK2Px3tdm0jKjARb-5Cj/edit#gid=2111119363

Now that I try the link I realize it opens in Google docs instead of just being able to download the excel file.

Some advice on how to upload the sample file would be appreciated :^)