Excluding a column from COUNTIF function

[Lux8]

Hello,

I have this formula that calculates a score based on a Yes or No response that is working fine - =COUNTIF(D2:AE2,"Yes")/(COUNTIF(D2:AE2,"Yes")+COUNTIF(D2:AE2,"No"))

However, I need to edit it to exclude Column "X" (which is in between my range of D2 to AE2), from the calculation. What is the best way to do so?

I tried editing it this way - =COUNTIF(D2:W2 : Y2:AE2,"Yes")/(COUNTIF(D2:AE2 : Y2:AE2,"Yes")+COUNTIF(D2:AE2 : Y2:AE2, "No")), but it still seems to be calculating "X" column's value.

 

[RoryA]

Your syntax is wrong, but that wouldn't work with COUNTIF anyway. Do any of the cells have values other than Yes or No? (eg blank)

 

[etaf]

can you subtract the values in column X

=COUNTIF(D2:AE2,"Yes")/(COUNTIF(D2:AE2,"Yes")+COUNTIF(D2:AE2,"No"))

=(COUNTIF(D2:AE2,"Yes") - COUNTIF(X2:X2,"Yes")) / ((COUNTIF(D2:AE2,"Yes")+COUNTIF(D2:AE2,"No"))) - what eles is in column X ???

=(COUNTIF(D2:AE2,"Yes") - COUNTIF(X2:X2,"Yes")) / ((COUNTIF(D2:AE2,"Yes")+COUNTIF(D2:AE2,"No")) - (COUNTIF(X2:X2,"Yes")+COUNTIF(X2:X2,"No")))
OR
perhaps a SUMPRODUCT

 

[Lux8]

Your syntax is wrong, but that wouldn't work with COUNTIF anyway. Do any of the cells have values other than Yes or No? (eg blank)

They do have NA as well, except for X column, which is Yes or No only

 

[RoryA]

Perhaps:

=(COUNTIF(D2:AE2,"Yes") - (X2="Yes")) / (COUNTIF(D2:AE2,"<>NA")-(X2<>"NA"))

 

[Lux8]

can you subtract the values in column X

=COUNTIF(D2:AE2,"Yes")/(COUNTIF(D2:AE2,"Yes")+COUNTIF(D2:AE2,"No"))

=(COUNTIF(D2:AE2,"Yes") - COUNTIF(X2:X2,"Yes")) / ((COUNTIF(D2:AE2,"Yes")+COUNTIF(D2:AE2,"No"))) - what eles is in column X ???

=(COUNTIF(D2:AE2,"Yes") - COUNTIF(X2:X2,"Yes")) / ((COUNTIF(D2:AE2,"Yes")+COUNTIF(D2:AE2,"No")) - (COUNTIF(X2:X2,"Yes")+COUNTIF(X2:X2,"No")))
OR
perhaps a SUMPRODUCT

I think i've managed to crack it using your logic of subtracting. Seems like this is working as expected - =(COUNTIF(D2:AE2,"Yes") - COUNTIF(X2:X2,"Yes")) / ((COUNTIF(D2:AE2,"Yes") - COUNTIF(X2:X2,"Yes")) + (COUNTIF(D2:AE2,"No") - COUNTIF(X2:X2,"No")))

 

[RoryA]

Actually, If col X is only ever Yes or No, you can just subtract 1 at the end:

=(COUNTIF(D2:AE2,"Yes") - (X2="Yes")) / (COUNTIF(D2:AE2,"<>NA")-1)