Count If from Roll Up

Hi all,

I am trying to count the number of occurrences of certain keywords from a sheet column into a rollup. I cannot seem to get the formulas right. Error coming up as #UNPARSEABLE

Keyword "HHS", Sheet Name "25", Column Name "Funding Agency"

=COUNTIF({25 Range 1}[Funding Agency]:[Funding Agency], "HHS")

A little additional context, the agency acronym is usually embedded in the larger block of text within the column --> National Institute of Environmental Health Sciences (HHS - NIH)

Thanks in advance for your help!

Find more posts tagged with

Formulas

Accepted answers

Bassam Khalil

@Virginian

Yes what you define as a variable the formula will use it to count. for your sample the formula will be:

 =COUNTIFS({25 Range 1}, @cell = "National Institute of Environmental Health Sciences")

Virginian

@Bassam Khalil Thank you! You helped me understand the larger picture.

Here is the formula that resulted for this use case: =COUNTIFS({25 Range 1}, CONTAINS("HHS", @cell))

All comments

Bassam Khalil

Hi @Virginian

Hope you are fine, if you try to count in the same sheet then the formula will be:

=COUNTIFS([Funding Agency]:[Funding Agency], @cell = "HHS")

the following screenshot shows the result:

Bassam Khalil

Hi @Virginian

If you try to make cross sheet formula then the formula will be:

=COUNTIFS({25 Range 1}, @cell = "HHS")

{25 Range 1} is the range of Funding Agency of the data sheet.

Virginian

@Bassam Khalil - thanks for your help! The formula appears to execute now, however, it is just showing 0 for all values. Does it matter the agency acronym is usually embedded in a larger block of text within the column -->Example: National Institute of Environmental Health Sciences (HHS - NIH)

Virginian

Also clarifying the formula provided does work if I provide the full name, like listed in the above example. So it must need to parse it to isolate "HHS"?

Bassam Khalil

@Virginian

Yes what you define as a variable the formula will use it to count. for your sample the formula will be:

 =COUNTIFS({25 Range 1}, @cell = "National Institute of Environmental Health Sciences")

Virginian

@Bassam Khalil Thank you! You helped me understand the larger picture.

Here is the formula that resulted for this use case: =COUNTIFS({25 Range 1}, CONTAINS("HHS", @cell))

Bassam Khalil

@Virginian

Excellent, i will be happy to help you any time.and I will be grateful for your "Vote Up" or "Insightful"

Sign in to join the conversation!

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.