How to reference specific column and row IDs in a COUNTIF formula in Excel?-CodePudding

I have a data sheet that looks like this.

     Proj1 Proj1 Proj1 Proj2 Proj2
Req1.  x    x            o     x       
Req2   x          x            x

I am using the below formula to check range B2:E2 on my data sheet and return results in the second sheet.

=IF(COUNTIF('Data'!B2:E2;"x")>0;"x";IF(COUNTBLANK('Data'!B2:E2)=COLUMNS('Data'!B2:E2);"0";"o"))

However instead of giving this range (B2:E2) in the formula, I would like to reference column & row ids (e.g. Proj1 & Req1) and run the formula for the data that matches this criteria.

CodePudding user response：

Try with SUMPRODUCT:

=IF(SUMPRODUCT(--($B$2:$E$5="x")*--($A$2:$A$5=I3))>0,"x",IF(SUMPRODUCT(--($B$2:$E$5="")*--($A$2:$A$5=I3))=4,0,"o"))

I've added manually the 4 because there are 4 columns but you could replace the 4 with COUNTA($B$1:$E$1)

CodePudding user response：

It should look like this if I've understood requirements correctly:

=LET(Proj,$B$1:$F$1,
Req,$A$2:$A$3,
Data,$B$2:$F$3,
Blanks,SUM((Proj=I$1)*(Req=$H2)*(Data="")),
x,SUM((Proj=I$1)*(Req=$H2)*(Data="x")),
IF(x>0,"x",IF(Blanks=COLUMNS(Data),0,"o")))

Just pulled down and across: