Unique + COUNTIF Formula, Need Help

Hi all.

My team is currently using a sheet to track tasks and hours spent on each task, for each week of the month. There are 4 different types of tasks (Project, Initiative, Ad Hoc, Daily Task), and some of out project managers work on the same project or initiative.

I am trying to create a COUNTIF formula to count the number of projects, initiatives, etc [Type column] for only unique names [Project / Initiative Name column]. Otherwise if I count the number of projects listed, I will be double or triple counting some projects.

I have tried to use the AI function to help, and have tried to do this on my own, but haven't been able to get this to work.

Find more posts tagged with

Formulas

Countif

Accepted answers

Darren Mullen

@Angelina Use COLLECT() to collect the values based on your criteria, then wrap it in distinct then count. Like this:

COUNT(DISTINCT(COLLECT(«YOUR RANGES AND CRITERIA HERE»)))

All comments

Darren Mullen

@Angelina Use COLLECT() to collect the values based on your criteria, then wrap it in distinct then count. Like this:

COUNT(DISTINCT(COLLECT(«YOUR RANGES AND CRITERIA HERE»)))

Angelina

Thank you so much!

Darren Mullen

@Angelina You're welcome. Glad that worked for you!

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.