Count If from Roll Up

VirginianVirginian
edited 10/14/21 in Formulas and Functions
10/14/21 Edited 10/14/21
Accepted

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!

Tags:

Best Answers

  • Bassam KhalilBassam Khalil ✭✭✭✭✭
    Accepted Answer

    @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")
    

    PMP Certified

    [email protected]

    www.mobilproject.it

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • VirginianVirginian
    Accepted Answer

    @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))

Answers

  • Bassam KhalilBassam 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:


    PMP Certified

    [email protected]

    www.mobilproject.it

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Bassam KhalilBassam Khalil ✭✭✭✭✭
    edited 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.

    PMP Certified

    [email protected]

    www.mobilproject.it

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • @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)

  • 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 KhalilBassam Khalil ✭✭✭✭✭
    Accepted Answer

    @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")
    

    PMP Certified

    [email protected]

    www.mobilproject.it

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • VirginianVirginian
    Accepted Answer

    @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 KhalilBassam Khalil ✭✭✭✭✭

    @Virginian 

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

    PMP Certified

    [email protected]

    www.mobilproject.it

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

Sign In or Register to comment.