# Formula Help Needed to find Distinct Client Interactions

Copper Contributor

# Formula Help Needed to find Distinct Client Interactions

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

# Re: Formula Help Needed to find Distinct Client Interactions

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.

# Re: Formula Help Needed to find Distinct Client Interactions

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.

# Re: Formula Help Needed to find Distinct Client Interactions

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.

# Re: Formula Help Needed to find Distinct Client Interactions

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: