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

**deadlyliquidxxx** · 02-12-2015, 05:55 PM

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

**oeldere** · 02-12-2015, 06:41 PM

Crossposting is forbidden on this forum.

http://www.mrexcel.com/forum/excel-q...ks-errors.html

Remark => it is not forbidden, but you have to add the link to the forum you placed the same question.

See the forumrules for crossposting.

See for the reason why the link below.

http://www.excelguru.ca/content.php?184

**oeldere** · 02-12-2015, 06:43 PM

Duplicated post.

**FDibbins** · 02-12-2015, 06:52 PM

Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

Now, having said that, I suggest you modify the formulas that are producing the error meaasages - ie, fix the problem, dont work areound it

Also, please include a sample WB so we can see what you are working with. THis avoids a lot of back and forth guesswork