SUMPRODUCT - NON-CONTIGUOUS problem

[The Grim Discovery]

Hiya

Could anyone advise me on how best to adjust the following formula? It works fine for calculating the % of 'Yes' /'Yes'+'No' across the range J2:AB500. However, I do not wish to include columns H, L, P and T from within this range. What's making this difficult is that columns H, L, P and T also contain a load of 'yes's and 'no's.

Here's the formula:

=SUMPRODUCT(ISNUMBER(MATCH(Data!$F$2:$F$500,{"Blue,"Red","White"},0))*ISNUMBER(MATCH(Data!J2:AB500,{"YES"},0)))/SUMPRODUCT(ISNUMBER(MATCH(Data!$F$2:$F$500,{"Blue","Red","White"},0))*ISNUMBER(MATCH(Data!J2:AB500,{"YES","NO"},0)))

Thanks in advance.

 

[Domenic]

It looks like Column H is already excluded from J2:AB500, so we only need to exclude Columns L, P, and T. Therefore, try the following formula, which needs to be confirmed with CONTROL+SHIFT+ENTER...

=SUM(IF(ISNUMBER(MATCH(Data!F2:F500,{"Blue","Red","White"},0)),IF(ISNA(MATCH(COLUMN(Data!J2:AB500),{12,16,20},0)),IF(Data!J2:AB500="YES",1))))/SUM(IF(ISNUMBER(MATCH(Data!F2:F500,{"Blue","Red","White"},0)),IF(ISNA(MATCH(COLUMN(Data!J2:AB500),{12,16,20},0)),IF(ISNUMBER(MATCH(Data!J2:AB500,{"YES","NO"},0)),1))))

Actually, for robustness, try the following formula instead...

=SUM(IF(ISNUMBER(MATCH(Data!F2:F500,{"Blue","Red","White"},0)),IF(ISNA(MATCH(COLUMN(Data!J2:AB500)-COLUMN(Data!J2)+1,{3,7,11},0)),IF(Data!J2:AB500="YES",1))))/SUM(IF(ISNUMBER(MATCH(Data!F2:F500,{"Blue","Red","White"},0)),IF(ISNA(MATCH(COLUMN(Data!J2:AB500)-COLUMN(Data!J2)+1,{3,7,11},0)),IF(ISNUMBER(MATCH(Data!J2:AB500,{"YES","NO"},0)),1))))

Note that the formula also needs to be confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[The Grim Discovery]

Hey Domenic

That is just brilliant - just what I was after. Thank you so very much indeed.
All the best.

 

[Domenic]

You're very welcome!

Cheers!