4

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:

example of Table 1

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:

example of Table 2

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:

example of results

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?

Improve this question
2
  • As the question is stated, it looks to fall into the XY Problem. Suggestion 1: Instead of asking how to use COUNTIFS, or any other specific function, the question should be edited to ask how to achieve the desired result. Suggestion 2: Move the section "Question" from the bottom to the top, as that is a good practice for technical / business writing (put the concrete call to action at the beginning then explain why). Suggestion 3: See How do I write a great question?
    – Rubén ♦
    Dec 17, 2015 at 18:12
  • Agreeing with Rubén: the focus of a question should be on the problem, not on what the solution might be.
    – user79865
    Dec 18, 2015 at 7:26

1 Answer 1

Reset to default
2

To put it simply, you want to find the size of the intersection of two arrays. Taking the intersection is discussed here. Adapting it to your case results in the following:

=counta(filter('Form responses 1'!C1:O1, 'Form responses 1'!C2:O2="Agree", match('Form responses 1'!C1:O1, C2:C21, 0)))

The filter command applies the required criteria, then counta counts the values that passed the filter.

Improve this answer
0

Your Answer

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

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