IF/AND Function?

[STRACTROOP]

The attached spreadsheet represents a test with Yes and No answers to 24 questions.  I'm trying to come up with a formula that tells me how many Yes or No's to each question.

For example, question 1 has 10 Yes's and 14 No's.

I tried Countif but always got an error when I tried to nest.

#	Question	Yes	No
Tom	1	1	0
Tom	2	0	1
Tom	3	1	0
Tom	4	1	0
Jerry	1	0	1
Jerry	2	0	1
Jerry	3	1	0
Jerry	4	1	0

[#2]

@STRACTROOP There are many ways to do this and I don't know that any one is much better then another.  Here is 1 option using SUMIFS

=SUMIFS(C$2:C$9,$B$2:$B$9,$A$14#)

where C2:C9 is the range of YES or NO and B2:B9 is the question #s and A14# is a spill range with the list of question #s (in the attached I used UNIQUE() but you could just as easily use SEQUENCE() or old school use ROW(A1:A8)

[#3]

@STRACTROOP You could simplify the process by having a single Answer column, where 1 = Yes and 0 = No. For example:

Name	Question	Answer
Tom	1	1
Tom	2	0
Tom	3	1
Tom	4	1
Jerry	1	0
Jerry	2	0
Jerry	3	1
Jerry	4	1

The COUNTIFS function can then be used to get a count of all Yes/No answers for each question. For example, to count all Yes answers for question 3:

=COUNTIFS(B2:B113, 3, C2:C113, 1)

And, to count all No answers for question 3:

=COUNTIFS(B2:B113, 3, C2:C113, 0)

With MS365, the results can also be made to spill dynamically for all questions and answers using TOCOL & UNIQUE for the question criteria and TOROW & SORT/UNIQUE for the answer criteria:

=COUNTIFS(B2:B113, TOCOL(UNIQUE(B2:B113), 1), C2:C113, TOROW(SORT(UNIQUE(C2:C113),, -1), 1))

The result in this case would be a 2x4 array representing a count of all Yes and No answers for each question. To create a single cell report, complete with question numbers as well as headers, use the following:

=LET(
q, TOCOL(UNIQUE(B2:B113), 1),
a, TOROW(SORT(UNIQUE(C2:C113),, -1), 1),
VSTACK({"Question","Yes","No"}, HSTACK(q, COUNTIFS(B2:B113, q, C2:C113, a))))

Adjust the range references to meet your needs. :)

[#4]

@STRACTROOP As a variant, how about a good old pivot table. That is, if you are not forced somehow to use spreadsheet functions.

Some examples in the attached file, with and without changing the set-up of your data. It doesn't really matter.