# Is there a formula to count occurrences of a string in another sheet when the string needs to be "looked up" in a different sheet? (Quiz example)

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"

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. Oct 17, 2022 at 15:23

• 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. Oct 19, 2022 at 21:45