Formula Help Needed to find Distinct Client Interactions

[RobDevens]

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!

[#2]

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.

[#3]

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.

[#4]

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.

[#5]

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 :^)