Search code examples
google-sheetscountifunique-values

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

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.

Solution

  • 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.