Count specific number of duplicate values in a given month

Hi there.

I'm working on site analytics. I'd like to be able to track my site's count of what I'm calling "regular users per month" (at least 4 visits per month).

I'm not having any luck writing a formula that can look only at January's items (Month # = 1) and count how many rows have a Clarity ID that's duplicated at least 4 times. That would show me how many users visited the site at least 4 times in January.

Am I overthinking this, or am I trying to do something that a formula isn't suited for?

Find more posts tagged with

Accepted answers

Paul Newcome

The checkbox formula is based off of the month and year for that clarity id number. So even though it is the same number, they didn't visit at least 4 times during that month, so they didn't get checked as a "regular".

The rest is hard to say without being able to see the clarity id. Is it possible for you to make a copy of this sheet and then replace the ids with mock ids just so we can see what is going on there?

Are there any clarity ids that are just numbers while others have some or all alphabetical characters?

All comments

Naeem Ejaz

a{text-decoration:none;color:#464feb}

It sounds like you're on the right track, but this task can be a bit complex for a simple formula. You might find it easier to use a combination of functions or even a script to achieve this. Here's a general approach using Excel or Google Sheets:

Filter for January: Use a filter to select only the rows where the month is January.
Count Visits: Use a COUNTIF function to count the number of visits per Clarity ID.
Identify Regular Users: Use a COUNTIF function again to count how many Clarity IDs have at least 4 visits.

Here's a step-by-step example:

Filter for January:

   =FILTER(A2:B100, MONTH(A2:A100) = 1)

Assuming column A has dates and column B has Clarity IDs.

Count Visits per Clarity ID:

   =COUNTIF(B2:B100, B2)

This counts the number of times each Clarity ID appears.

Identify Regular Users:

   =COUNTIF(C2:C100, ">=4")

Assuming column C has the counts of visits per Clarity ID.

Zach_

Thank you, @Naeem Ejaz. I added an additional column and reworked a couple of formulas, and it worked.

Zach_

@Naeem Ejaz, I have one more question.

How can I modify my formula so that it it includes an if() function and displays "" when it's currently displaying "0?" The "0" display on future months makes my chart (below) unattractive.

Here is my exact formula (month of January): =COUNTIFS([Count Per Clarity ID]:[Count Per Clarity ID], >=20, [Month #]:[Month #], 1)

I already tried to nest the above into an IF(ISBLANK) formula, but I couldn't get it to work.

Paul Newcome

You should only need one single helper column (and not the month column). This helper column (called "Regular User" in this example) would simply be a checkbox column with the following column formula:

=IF(COUNTIFS([Clarity ID]:[Clarity ID], @cell = [Clarity ID]@row, Date:Date, AND(IFERROR(MONTH(@cell), 0) = MONTH(Date@row), IFERROR(YEAR(@cell), 0) = YEAR(Date@row))) >= 4, 1)

This should check the box on every row that has a [Clarity ID] entered at least 4 times in the same month/year as Date@row.

Then to get your counts, you would just need this:

=COUNT(DISTINCT(COLLECT([Clarity ID]:[Clarity ID], [Regular User]:[Regular User], @cell = 1, Date:Date, AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = 2025))))

The above will give you Jan 2025. Changing the last 1 to a 2 will give you Feb of 2025 and changing 2025 will give you a different year.

Then to make this show blanks instead of zeros, you would wrap the whole COUNT formula in an IF statement.

=IF(COUNT(DISTINCT(COLLECT(……………)))) <> 0, COUNT(DISTINCT(COLLECT(……………………)))))

Zach_

Thanks for the response, @Paul Newcome.

That checkbox formula works great. But I'm not having much luck with the second formula you shared (the one to produce the count of users who visited >= 4 times). It displays "1" for every month, even when hundreds of the "Regular User" checkboxes are checked.

Here's the formula:

=COUNT(DISTINCT(COLLECT([Clarity ID]:[Clarity ID], [Regular User]:[Regular User], @cell = 1, Date:Date, AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = 2025))))

Naeem Ejaz

To modify your formula so that it displays an empty string ("") instead of "0," you can use the IF function to check if the result of your COUNTIFS formula is zero. If it is, you can return an empty string; otherwise, return the result of the COUNTIFS formula. Here's how you can do it:

=IF(COUNTIFS([Count Per Clarity ID]:[Count Per Clarity ID], ">=20", [Month #]:[Month #], 1) = 0, "", COUNTIFS([Count Per Clarity ID]:[Count Per Clarity ID], ">=20", [Month #]:[Month #], 1))

This formula checks if the result of COUNTIFS is zero. If it is, it returns an empty string (""). Otherwise, it returns the actual count.

Paul Newcome

@Zach_ That formula specifically is for January of 2025. Did you update it for the rest of the months? Are you able to provide a screenshot showing the source data where there should be a different number other than that output by the formula?

Zach_

@Paul Newcome, yes, I adjusted it for December 2024, January 2025, and February 2025. It displays "1" for each, despite each month having more "Regular User" boxes that are checked than that.

Here's a look at the source data.

As you can see, there are a lot of January users with checked "Regular Users" boxes. (As a test, I even lowered the threshold in the formula you provided down from 4 to 2.) I don't see why the formula below displays "1" when it should be counting how many users with a January visit date ("Date") have a checked "Regular User" box.

=COUNT(DISTINCT(COLLECT([Clarity ID]:[Clarity ID], [Regular User]:[Regular User], @cell = 1, Date:Date, AND(IFERROR(MONTH(@cell ), 0) = 1, IFERROR(YEAR(@cell ), 0) = 2025))))@cell

Additionally, the "Regular User" checkbox is working inconsistently. As you can see below, for the same user, the "Regular User" box is unchecked for only 6 of their 7 visits. I don't understand why the formula failed to check the box in row 629.

Here's an example of it doing the same thing for another user:

Paul Newcome

The rest is hard to say without being able to see the clarity id. Is it possible for you to make a copy of this sheet and then replace the ids with mock ids just so we can see what is going on there?

Are there any clarity ids that are just numbers while others have some or all alphabetical characters?

Zach_

Hi again, @Paul Newcome. Using the advice you provided, I was able to get it to work exactly as desired, through some trial and error. My formatting was preventing your formulas from working. Thank you very much.

Paul Newcome

Happy to help. 👍️

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.