Did changing the column title permanently break the formula from sheet to sheet?

PhyllisB

I have a dashboard where I use a calculations sheet in between the dashboard and the sub sheets. For each, the formula is =COUNTIF({Type of Resource}, "Council Publication") with the item in quotes changing for each resource being tracked on different lines. The outcome continues to say zero, when there are counts to capture. In this situation, a new option was added for resources within its pull down menu within the subsheet, the title has changed for that column, and in the problem solving mania, the title for the range reference name has changed. In these moments I have tried saves and refreshes, created a new column and copying over all but the title of the column and deleting original to catch debris coding that may be present, and nothing I do seems to change it. Such a simple pull of data, but about to bring down this kingdom. Ideas? You'll see in illustrations that I added a second area to reproduce this trouble reporting block to try and see if re-entering would help…nope. Also when I change the range name to try and match the column I often get an error I cannot name them the same even though in other blocks that is the case. I have no idea why "Introduction to Council" is giving me 9 when the count for that is higher. Thanks for any help offered and Happy Holidays!

Melissa Yamada

Hello @PhyllisB

Since you're using a multi-select dropdown on your source sheet, your current formula doesn't count rows with multiple values.

In this case, you'll need to incorporate the HAS function on your formula.

Try this:

=COUNTIF({Type of Resource}, HAS(@cell, "Introduction to Council"))

Melissa Yamada

Hello @PhyllisB

Since you're using a multi-select dropdown on your source sheet, your current formula doesn't count rows with multiple values.

In this case, you'll need to incorporate the HAS function on your formula.

Try this:

=COUNTIF({Type of Resource}, HAS(@cell, "Introduction to Council"))

PhyllisB

@Melissa Yamada you are awesome. That fixed it.

Melissa Yamada

Happy to help! 😊