Sumproduct keeps giving me a problem, how can I formulate to skip blanks/errors

[deadlyliquidx]

Basically I am working with a spreadsheet with alotta REF!, NA, Texts and blanks within the data array and I dont have the time to manually remove them,
how can I make this formula to ignore all those errors and blanks in the data array without having to manually change data to suit the formula.
=SUMPRODUCT(('2015OTB'!K16:N23)*('2015OTB'!A16:A23=A16)*(ISNUMBER(MATCH(K2:N2,A1:A2,0)))*(ISNUMBER(MATCH(K14:N14,A3:A4,0))))

thanks

 

[jumbledore]

what does the exclamation sign do? shouldn't it be '<>'???. And I think the syntax for sumproduct is sumproduct(arr1,arr2,arr3,...,). You have '*' in your formula

 

[Aladin Akyurek]

Basically I am working with a spreadsheet with alotta REF!, NA, Texts and blanks within the data array and I dont have the time to manually remove them,
how can I make this formula to ignore all those errors and blanks in the data array without having to manually change data to suit the formula.
=SUMPRODUCT(('2015OTB'!K16:N23)*('2015OTB'!A16:A23=A16)*(ISNUMBER(MATCH(K2:N2,A1:A2,0)))*(ISNUMBER(MATCH(K14:N14,A3:A4,0))))

thanks

Aren't K2:N2 and K14:N14 ranges in 2015OTB?

 

[jumbledore]

Aren't K2:N2 and K14:N14 ranges in 2015OTB?

oh yes sorry that's my mistake. But is the syntax correct? should the '*' be replaced by commas?

 

[Aladin Akyurek]

oh yes sorry that's my mistake.

Are you the OP? I thought that question should be answered by the OP...

But is the syntax correct? should the '*' be replaced by commas?

No. The references the formula is given to process are not compatible with the comma syntax. The problem the OP has not directly related to syntax though.

 

[deadlyliquidx]

Are you the OP? I thought that question should be answered by the OP...



No. The references the formula is given to process are not compatible with the comma syntax. The problem the OP has not directly related to syntax though.

I dunno what you guys are asking but I know the formula works given no blanks, errors are on the sheet. I just need the formula to skip errors you guys

 

[Aladin Akyurek]

I dunno what you guys are asking but I know the formula works given no blanks, errors are on the sheet. I just need the formula to skip errors you guys

What is the answer to the question of post #3?