I am keeping track of quiz results and trying to analyse our performance over multiple categories.
On one tab we keep track of each question's result (correct or incorrect) by date, and on another tab we keep track of each question's category, also by date.
I'm trying to create a third tab where we list each category, the total number of times a question in that category has appeared, and the total number of times we've correctly answered a question in that category.
All objectives but the very last are simple enough, I cannot find a formula combination that can take the position of a question based on the category and date, and find the corresponding question result (and then sum them)
An example spreadsheet can be found here, but here's a brief description of each tab:
Results:
DATE Q1 Q2 Q3 Q4
1/1/23 1 0 1 1
1/2/23 1 0 1 0
1/3/23 0 1 1 1
Questions:
DATE Q1 Q2 Q3 Q4
1/1/23 History Animals Sports Music
1/2/23 Sports Music Geography Movies
1/3/23 Movies Music Movies History
Categories:
CATEGORY COUNT 'TIMES CORRECT'
Animals 1 This is what I'm looking for
Geography 1
History 2
Movies 3
Music 3
Sports 2
I have tried various combinations of INDEX/MATCH, FILTER, QUERY but can't seem to find a combination that allows me to specify the position of one instance of a question (based on it's category) then find the corresponding result, then sum them in a single cell.
Use a Countifs with the two ranges
=COUNTIFS(Questions!$B$2:$K,A2,Results!$B$2:$K,1)