2

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.

A.Be
  • 73
  • 5

2 Answers2

0

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.

tomf
  • 525
  • 3
  • 10
  • 1
    Thank you so much! This looks pretty great and I did check your sheet but I do not see any formulas .. how exactly did you do it and how can I use this code to replicate it? – A.Be Jun 09 '22 at 11:39
  • Check the 'UPDATED--overviewTomf' sheet in the yellow cells. It's doing one formula per table – tomf Jun 09 '22 at 16:51
  • 1
    Holy crap, this is really really good. Thank you! The only thing is it's not getting it right for Warner and for the column "accounts with 3 meetings". For some reason it shows 0 there, but he should have 1. – A.Be Jun 09 '22 at 17:36
  • Do you want 3 meetings to be 3 and under meetings or do you want someone with 4 meetings to have that counted in the 3 column? – tomf Jun 09 '22 at 18:45
  • 1
    I would need it to show 3 and more meetings there – A.Be Jun 11 '22 at 16:48
  • perfecto, @A.Be -- check the tomf sheet under F. There are two formulas that cover it. – tomf Jun 11 '22 at 22:12
0

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.

Lorena Gomez
  • 1,946
  • 2
  • 4
  • 11
  • Thank you so much! This will do the trick! – A.Be Jun 09 '22 at 11:38
  • One more question. Is there a way I can include in the formula 3+? For example, one formula that shows unique count of accounts for 1 meeting, one for 2 meetings and the the third one is supposed to show the unique count of accounts with 3 and more meetings. How can I incorporate that in the formula? – A.Be Jun 09 '22 at 13:54
  • @A.Be yes you can adjust the formula for more than 3 meetings. Just go to the formula and at the end, instead of 3, enter the following: ">=3" and then just drag the formula to the end of the table. I have updated your test sheet. – Lorena Gomez Jun 09 '22 at 15:12
  • I tried that, but unfortunately this gives me an error – A.Be Jun 09 '22 at 15:37
  • I tried it in the `copyOverview` tab in your test sheet, the complete formula for the first table looks like this: `=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));">=3")})` and 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));">=3")})` – Lorena Gomez Jun 09 '22 at 15:49
  • I see! Thank you so much. Looks like in this case we need to include the " " - which I didn't. Thanks a ton – A.Be Jun 09 '22 at 16:47
  • @A.Be If this answered your question, please click the accept button on the left (check icon). By doing so, other people in the community, who may have the same concern as you, will know that theirs can be resolved. If the accept button is unavailable to you, feel free to tell me. [how to accept answer](https://stackoverflow.com/help/accepted-answer) – Lorena Gomez Jun 29 '22 at 16:14