Forum Discussion
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 |
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)
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. 🙂
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.
