Formula for counting multiple cells

Hello,

I am struggling with formulas on my calculation sheet that references my main sheet. In the example below, I am trying to count every time my name appears (which could be in several different columns) if one column has a certain activity selected (in the example below this is "internal customer engagement".

Can someone figure out what is wrong with my formula below and how it should read?

=COUNTIFS({2023_MA Project Management System Range 1}, "Internal Customer Engagement", {2024 MA Activity Management System Range 3}, HAS(@cell, "Lindsay Megenhardt") + COUNTIFS({2024 MA Activity Management System Range 4}, HAS(@cell, "Lindsay Megenhardt")) + COUNTIFS({2024 MA Activity Management System Range 5}, HAS(@cell, "Lindsay Megenhardt")))

Find more posts tagged with

Accepted answers

All comments

gregthomas

Quick question - will Lindsey be in a Contact type column in all of these instances, or Text? Or a mix of column types?

Lindsay Megenhardt

The columns with my name are populated using a Form that has a drop down menu with my name and others to choose from. Does that answer your question?

Genevieve P.

Hi @Lindsay Megenhardt

It looks like you're just missing a closing parentheses after the first COUNTIFS 🙂

=COUNTIFS(.... HAS()) < this one. We close off the HAS() but then also need to close off the COUNTIFS.

I assume you also need to filter by the first criteria in your other two COUNTIFS, is that correct? Try:

=COUNTIFS({2023_MA Project Management System Range 1}, "Internal Customer Engagement", {2024 MA Activity Management System Range 3}, HAS(@cell, "Lindsay Megenhardt")) + COUNTIFS({2023_MA Project Management System Range 1}, "Internal Customer Engagement", {2024 MA Activity Management System Range 4}, HAS(@cell, "Lindsay Megenhardt")) + COUNTIFS({2023_MA Project Management System Range 1}, "Internal Customer Engagement", {2024 MA Activity Management System Range 5}, HAS(@cell, "Lindsay Megenhardt"))

Keep in mind that if your name appears three times in the row with "Internal Customer Engagement" selected, this will count 3x.

Cheers,

Genevieve

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.