Count If from Roll Up

Virginian · 10/14/21

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!

Bassam Khalil · 10/14/21

@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 · 10/14/21

@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 · 10/14/21

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 · 10/14/21

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 · 10/14/21

@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 · 10/14/21

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 · 10/14/21

@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 · 10/14/21

@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 · 10/14/21

@Virginian

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

Count If from Roll Up

Best Answers

Answers

Help Article Resources

Categories