Sorry for poor title wording.

I am keeping track of quiz questions in a Google Spreadsheet. In order to generate question statistics I also keep track of some metadata about each theme in a different sheet. Finally, I want to show theme metadata statistics grouped by each quiz in a different sheet. All data is manually entered, except for the numbers which I want to be generated from a formula. Consider the following:

Sheet name: "Quiz 1"

Theme Question Answer
Super Mario What is Mario's occupation? Plumber
Call of Duty What is Soap's real name? John MacTavish
Tunic What animal is the player? A fox

Sheet name: "Themes"

Theme Popularity
Super Mario Well-known
Call of Duty Well-known
Tunic Unknown

Sheet name: "Stats"

Popularity Quiz #1
Unknown 1
Well-known 2

What is a formula/function I can use to calculate the numbers in the stats sheet?

Edit: Alternatively, would it be easier to group/structure the data differently?

  • Question is unclear. You can Improve it by adding column and row references to your examples and giving a name to each table, then letting us know what a calculation would do. e. g. In table 3, column 2 I want to calculate how many times value x shows up in table 2 column 4 where it also equals the value in table 1 column 1 but is greater than 7.
    – Blindspots
    Oct 17, 2022 at 15:23

1 Answer 1

  • each table in its own sheet
  • each table starts at cell A1
  • sheet names : "Quiz 1", "Quiz 2", "Stats"
# In sheet "Stats":

         A                   B              
   1 | Popularity |         Quiz #2              |
   2 | Unknown    | =COUNTIF('Quiz 1'!$B$2:B,A2) |
   3 | Well-known | =COUNTIF('Quiz 1'!$B$2:B,A3) |
  • Sorry for the confusion. I made an edit to hopefully make it more clear.
    – Godspark
    Oct 19, 2022 at 21:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.