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

Answers

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

  • 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.
    Genevieve P. Employee
    edited 03/13/24

    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

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!