Formula for counting multiple cells

Lindsay Megenhardt

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

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