#Countif formular referencing multiple subgrids to show average rating results

Reni Viessmann

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

Paul Newcome

You can only reference one different sheet within a function, so you will need to use a COUNTIFS for the first sheet, then another for the second sheet and another for the third, so on and so forth. Then you would add them together.

=COUNTIFS({First Sheet}, [Conference Name]@row) + COUNTIFS({Second Sheet}, [Conference Name]@row) + COUNTIFS({Third Sheet}, [Conference Name]@row) + ........................................

Reni Viessmann

Hi Paul, sometimes its a simple "+" ... I was thinking too complicated

I appreciate your answer so much. It worked for my total number of submitted votings. Since I just have one condition within a formula I tried it with countif({First Sheet}, [Conference Name]@row) .... and that is working here as well.

THANX!

Paul Newcome

Happy to help. 👍️