# IF/AND Function?

Copper Contributor

# IF/AND Function?

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

3 Replies

# Re: IF/AND Function?

@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)

# Re: IF/AND Function?

@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))))``````