#Countif formular referencing multiple subgrids to show average rating results

Dear Community I am looking for the appropriate formular to calculate an #average recommendation rate.

In my mastersheet "2021 Science & Tech Conferences" I am summarizing all feedback results of different conferences. The feedbacks per conference are collected in multiple subgrids (8 in total). The subgrids combine different conferences which belong to a same Topic Area. I was already successful with my formulas which are calculating average feedback ratings in my mastersheet . But now I am struggling with the average recommendation rate per congress.

The subrids i.e. "Feedback Analytical Development" contain all the column "Recommendation" as single-seclet dropdown: yes / no - see Screenshot

In my mastersheet I added a helper column - "no. of ratings" . Here I would like to count all feedback entries per conference and tried it with a "Countif formular"+ "or" to lookup the "Conference name@"row across all my 8 subgrids ... it doesn't work. Which formular works best for my purpose?

=COUNTIFS([Conference name]@row, OR({Feedback Analytical Development Range 1} = [Conference name]@row, {Feedback Bioprocess Development Range 1} = [Conference name]@row))

Next challenge is to calculate the average recommendation rate based on all "yes" and total number of votings ....

I would be so happy if anyone could help

Best Reni


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!