Forum Discussion
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=100903383710521729716&rtpof=true&sd=true
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!
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.
