Cell-linking - counting values from a sheet into the cell of another sheet

axelrodo

Hi everyone,

I'm trying to create an event summary sheet that captures data from individual event sheets to provide a live overview on where we're at with completion, health, risks, status, etc.

And so I have a cell "Health" in my specific event sheet that has a number of dots (red, green, gray) depending on with the task is completed, in progress, or at risk.

In my event summary sheet, I'd like to have a cell that counts all the red dots from the event sheet but I'm struggling with COUNTIF and the range, getting messages such as cells need to be next to each other (which they are). Does someone have a possible solution please?

Lucas Rayala

When you are creating your cross sheet reference and you select the range, @axelrodo, if you can select the entire column by clicking the column header, you might have better luck. The formula is simply:

=COUNTIFS({Health summary}, "Red")

I prefer COUNTIFS vs COUNTIF, as it's easier to expand to more criteria if you want.

axelrodo

Hi Lucas, thanks for helping. I've the recommended formula in the cell and linked it with the Health column from the other sheet (the column with the red dots) and it showed UNPARSEABLE. The formula read as: =COUNTIFS({{NYE 2023 - Event Range 2}}, "Red"). Not sure why it's not working.

axelrodo

Just tried a different way using: =COUNTIF({Status}, "Red") and it worked! All good for now ;-)

axelrodo

Andrée Starå

Hi @axelrodo

I hope you're well and safe!

Excellent! Glad you got it working!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Please support the Community by marking the post(s) that helped or answered your question or solved your problem with the accepted answer/helpful, Insightful/Vote Up/Awesome. It will make it easier for others to find a solution or help to answer!