Google Sheets Formula: Show count of a unique value if appears 1, 2, 3 times-CodePudding

I am struggling a bit to find the right formula here and I was hoping someone can help me with this.

I have a sheet with a report that shows all meetings from different sales reps for this year so far. As an overview, I want to list how many accounts we have in total with 1 meeting, how many accounts with 2 meetings, how many accounts with 3 meetings and so on..

So for example, in my sheet it should be a formula that shows the unique count for "Account Name" if it appears once, twice or three times. Outcome could be (for example):

Accounts with 1 meeting: 10

Accounts with 2 meetings: 4

Accounts with 3 meetings: 2

I have shared the sheet with you in this post so you have access to it and play around. Ideally, the formula should include the date cells to only look at meetings for that specific time frame. And it should also include the account tiers to show accounts only in specific tears.

I work with countifs before to show the count a record, which basically says "Show me the count of all values within this time period", but this is a bit different because the formula needs to say: "Show me the count of all unique values that appear once/twice/three times within this time period"

Hope I was able to articulate what I need help with.

CodePudding user response：

I'm sure there is a better way to do this but you can also try with the following formula:

For the first table:

=ARRAYFORMULA({countif(countifs(FILTER(Meetings!$A$2:$A; Meetings!$C$2:$C = $A4;Meetings!$D$2:$D>=$G$2;Meetings!$D$2:$D<=$I$2);UNIQUE(Meetings!$A$2:$A));1)})

You will need to paste that formula in each column, change the last number in the formula to 1, 2 or 3 (depending on the meetings you want to count) and then just drag the formula to the end of the table.

For the second table:

=ARRAYFORMULA({countif(countifs(FILTER(Meetings!$A$2:$A;(Meetings!$B$2:$B = "A") (Meetings!$B$2:$B = "B"); Meetings!$C$2:$C = $A16;Meetings!$D$2:$D>=$G$2;Meetings!$D$2:$D<=$I$2);UNIQUE(Meetings!$A$2:$A));1)})

This formula should be added to each column as well, I added an example to your sheet in tab copyOverview.

CodePudding user response：

big edit: this is what you're after

=IFERROR(
  QUERY(
   QUERY(
    {Meetings!$A$2:$D};
    "select Col1, Col2, Col3, Count(Col1)
     where 
     Col1 is not null and
     Col4 >= date '"&TEXT($G$2;"yyyy-mm-dd")&"' and
     Col4 <= date '"&TEXT($I$2;"yyyy-mm-dd")&"'
     group by Col1, Col2, Col3
     label Count(Col1) ''");
   "select Col3, Count(Col3)
    where
     Col2 matches 'A|B' and
     Col4 <= 3
    group by Col3
    pivot Col4"))

This will spit it all out. Check the tomf sheet to see it in action.