What I have:
Table 1:
Name | 1 | 2
Peter | Agree | Disagree
So Row 1 will be Name followed by the number 1 - 80. Row 2 will be the name followed by either "Agree" or "Disagree" depending on an answer the user gives on a form.
Example:
Table 2:
Title1 | Title2 | Title3 | Title4
1 | 2 | 3 | 4
5 | 6 | 7 | 8
So Row one is a title row and each title has a column of numbers.
Example:
What I want:
I want to be able to count the number of times a person has put the word "Agree" in table 1 but only for the numbers that appear in each title column in table 2.
Example: If a person put the word "Agree" in table 1 under the numbers 1, 3 and 5, then the total for Title1 in table 2 would be 2 where Title2 would be 0, Title3 would be 1, and Title4 would be 0.
Example:
You can see that the last row of each column is how many of the numbers in the Column have the word "Agree" in the other table under the same number.
What I have tried:
So far I am trying to get this working with a COUNTIFS formula. The issue here lies with the fact that I am trying to use a range as BOTH the Criteria Range and the Criteria.
So, to get around this, I followed the advice given in This Question and came up with the following:
=sum(arrayformula(COUNTIF('Form responses 1'!C1:CD1,C2:C21)))
This works but obviously doesn't take in to consideration the second part of the criteria. My obvious next thought was to try the following:
=SUMPRODUCT(COUNTIFS('Form responses 1'!C1:CD1,C2:C21,'Form responses 1'!C2:CD2,"Agree"))
The issue here is that for some reason is always returns 0 as it doesn't seem to be reading the second criteria.
Question:
Is there a way to have a COUNTIFS formula where both the criteria range and the criteria of the 1 check can be ranges and the the second check can be a standard criteria range and a word as the criteria?