Forum Discussion

STRACTROOP's avatar
STRACTROOP
Copper Contributor
Nov 06, 2023

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...
Formulas and Functions
djclements's avatar
djclements
Bronze Contributor
Nov 07, 2023

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

 

NameQuestionAnswer
Tom11
Tom20
Tom31
Tom41
Jerry10
Jerry20
Jerry31
Jerry41

 

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. 🙂

 

Resources