Imagine we have a dataset in Google Sheets representing a grading book. Columns E, G, I, K, and M represent the score one has achieved for questions 1 to 5, and rows 5 to 64 are the student names. I want to see how many of the students have solved at least 3 questions; Here, by solving I mean that the student has gotten a full mark on that question (also the grade distribution can vary; for example, question 1 has 10 points while the other have 25 points). Note that one thing that popped into my mind was to create a new column and store the number of solved questions for each student there (and then iterate over them and see how many of them are >= 3); Is there a way to satisfy the problem without creating or using new row/columns?

I didn't find anything proper that had to deal with rows and also keeping track of the cell count in those rows. One approach is to use Inclusion–exclusion principle with this link here. It'd basically be something like COUNTIFS(E5:E64,E4,G5:G64,G4,I5:I64,I4) COUNTIFS(E5:E64,E4,G5:G64,G4,K5:K64,K4) COUNTIFS(E5:E64,E4,G5:G64,G4,M5:M64,M4) COUNTIFS(E5:E64,E4,I5:I64,I4,K5:K64,K4) COUNTIFS(E5:E64,E4,I5:I64,I4,M5:M64,M4) COUNTIFS(E5:E64,E4,K5:K64,K4,M5:M64,M4) COUNTIFS(G5:G64,G4,I5:I64,I4,K5:K64,K4) COUNTIFS(G5:G64,G4,I5:I64,I4,M5:M64,M4) COUNTIFS(G5:G64,G4,K5:K64,K4,M5:M64,M4) COUNTIFS(I5:I64,I4,K5:K64,K4,M5:M64,M4) - (COUNTIFS(E5:E64,E4,G5:G64,G4,I5:I64,I4,K5:K64,K4) COUNTIFS(E5:E64,E4,G5:G64,G4,I5:I64,I4,M5:M64,M4) COUNTIFS(E5:E64,E4,G5:G64,G4,K5:K64,K4,M5:M64,M4) COUNTIFS(E5:E64,E4,I5:I64,I4,K5:K64,K4,M5:M64,M4) COUNTIFS(G5:G64,G4,I5:I64,I4,K5:K64,K4,M5:M64,M4) - COUNTIFS(E5:E64,E4,G5:G64,G4,I5:I64,I4,K5:K64,K4,M5:M64,M4))

this link was the closest I got. I think using matrices and multiplying them could be the solution. However, I'm not very good at that! I'd appreciate any help. Thanks in advance.

Update: here is a table to better understand this problem. The formula should return 2 (w and z both are satisfiable).

CodePudding user response：

This should cover it:

=SUMPRODUCT(--(((E5:E64=E4) (G5:G64=G4) (I5:I64=I4) (K5:K64=K4) (M5:M64=M4))>=3))