Count Values of X if Y is not the same for the other Xs

lisalettieri

Hi, thought I could use some variation of

=COUNT(DISTINCT(COLLECT({Advisor Rep ID}, IF{Client for the rep id is not equal to any of their other on the sheet}, WHERE {Case Consultation Meeting Number}, =1, {Submission Year}, =2024, {Status}, <>"Not a Case")))

But try as I might, can't get it to work. Thanks for your help!

Carson Penticuff

You probably grabbed the formulas before I corrected them in my last post, my apologies for that.

Regarding the square brackets, they are only needed if the column name contains a number, special character, or space. In other words, they are not needed if the column name contains only letters. It is fine to add the brackets into the formula, but you will notice that after typing or pasting the formula into the cell, the brackets will automatically be removed anywhere they are not needed if you go back into the cell to edit the formula.

Carson Penticuff

Are you able to post a screenshot of this sheet? Even if it has "dummy" data… I am having a bit of trouble following exactly what you are attempting to do.

lisalettieri

We need to count how many advisors used our services once for Meeting #1; 2-4 times; and more than 4 times for different clients (we are financial planners) within 2024.

1. We need to count the unique advisors, that came for Meeting #1, for different clients. They only used us once.

2. Then we need the advisors that came back 2-4 times for different clients, just Meeting 1, i.e. Moderate users.

3. Then over 4 times for different clients, just Meeting 1, i.e. Heavy users.

Does that help?

lisalettieri

I think I've got the number of Advisors that only used us once in 2024

=COUNT(DISTINCT(COLLECT({Advisor Namex}, {Meeting Number}, =1, {Submission Yearx}, =2024)))

How do I get Advisors used us 2-4 times, with above criteria? And over 4 times? Thanks!

Carson Penticuff

I am still a little confused, but using your example that appears to be working, these formulas may work for the 2-4 and >4 conditions:

=COUNT(DISTINCT(COLLECT({Advisor Namex}, {Meeting Number}, @cell >= 2, {Meeting Number}, @cell <= 4, {Submission Yearx}, 2024)))

=COUNT(DISTINCT(COLLECT({Advisor Namex}, {Meeting Number}, @cell > 4, {Submission Yearx}, 2024)))

If this is not what you are looking for, I am not sure what the meeting number represents. Is this the number of meetings a specific advisor attended during the year? So for the advisor that is listed three times with three different clients, does that mean he attended one meeting, but represented three clients during that meeting?

lisalettieri

Hi Carson, sorry it is complicated. Meeting Number is just the 1st meeting Advisor has with a particular client.

I'll try your formula, thanks.

lisalettieri

We want to Count the occurrences of Advisors that return with different clients. The Meeting number 1 means that we had a 1st meeting with that advisor regarding that client so it's a good way to get a count of new cases brought to us by Advisors. Joe Advisor came back for our services 4 times with four different clients. We want to count that as 1 Advisor who came back 2-4 times with New Clients' Cases.

Return the number of Advisors with "Meeting #1 occurring 2-4 times in 2024." 🤞

lisalettieri

I thought of another way to ask the question that might be helpful:

"Count the number of Advisors that came for 2-4 Meeting Ones." Joe brought us 4 different clients so we want to know Joe did that.

If an advisor came to us with 5 clients, we want to know that too with

"Count the number of Advisors that came for more than 4 Meeting Ones."

Does that help?

Carson Penticuff

I definitely was not understanding the meaning of "Meeting 1" but I think I have it now.

I'm not sure of a way to do this with only a formula. Are you able to add a helper column to your source sheet? If so, this should work. The helper column can be hidden.

Helper column formula for source sheet:
=COUNTIFS(Advisor:Advisor, Advisor@row, [Meeting Number]:[Meeting Number], @cell = 1)

Formula for 2-4
=COUNT(DISTINCT(COLLECT({Advisor Namex}, {Meeting 1 Helper}, @cell >= 2, {Meeting 1 Helper}, @cell >= 4)))

Formula for > 4
=COUNT(DISTINCT(COLLECT({Advisor Namex}, {Meeting 1 Helper}, @cell > 4)))

lisalettieri

Yup I thought we'd need that Helper column, thanks! The Helper column is counting how many Advisors that came used us, right? I can use it for another metric we're measuring too. I'll let you know how it goes. Thanks, Carson

Carson Penticuff

The helper column counts the number of times the advisor appears with 1 in the Meeting Number column. If a given advisor is listed with four different clients, the helper column will show 4 on each of those rows. I did miss the "2024" condition in my previous post, so here are the formulas revised to include the year.

Helper column formula for source sheet:

=COUNTIFS(Advisor:Advisor, Advisor@row, [Submission Year]:[Submission Year], @cell = 2024, [Meeting Number]:[Meeting Number], @cell = 1)

Formula for 2-4
=COUNT(DISTINCT(COLLECT({Advisor Namex}, {Submission Yearx}, @cell = 2024, {Meeting 1 Helper}, @cell >= 2, {Meeting 1 Helper}, @cell >= 4)))

Formula for > 4
=COUNT(DISTINCT(COLLECT({Advisor Namex}, {Submission Yearx}, @cell = 2024, {Meeting 1 Helper}, @cell > 4)))

lisalettieri

I am getting Unparseable. Advisor Name is the Column Name.

Helper column formula for source sheet:

=COUNTIFS(Advisor:Advisor, Advisor@row, [Meeting Number]:[Meeting Number], @cell = 1)

What I did

=COUNTIFS(Advisor Name:Advisor Name, Advisor Name@row, [Meeting Number]:[Meeting Number], @cell = 1)

Any suggestions to make it work? I'm not as familiar with formulas within the data sheet.

Carson Penticuff

You probably grabbed the formulas before I corrected them in my last post, my apologies for that.

Regarding the square brackets, they are only needed if the column name contains a number, special character, or space. In other words, they are not needed if the column name contains only letters. It is fine to add the brackets into the formula, but you will notice that after typing or pasting the formula into the cell, the brackets will automatically be removed anywhere they are not needed if you go back into the cell to edit the formula.

lisalettieri

I'll try thx!

lisalettieri

Still "Unparseable" sorry.

I pasted this in

=COUNTIFS(Advisor:Advisor, Advisor@row, [Submission Year]:[Submission Year], @cell = 2024, [Meeting Number]:[Meeting Number], @cell = 1)

changing Advisor column name to Advisor Name, since that's what the column is called. So it was

=COUNTIFS(Advisor Name:Advisor Name, Advisor Name@row, [Submission Year]:[Submission Year], @cell = 2024, [Meeting Number]:[Meeting Number], @cell = 1)

But no luck.

Carson Penticuff

If the column name has a space you will need the square braces, so, [Advisor Name]:[Advisor Name] and [Advisor Name]@row.