SUMPRODUCT arrays different size

Hi Folks:

I have the following issue:


excel.jpg

I need to check if all the grades set in A5:A18 are available in E5:E8.

For this, I am trying to use the SUMPRODUCT function to count the errors but it does not work because the arrays do not have the same size.

Basically my column A will receive 1,000+ values, I just need to count the number of values in this array which do not match with any of the values in E5:E8.

Any ideas? Thanks.

How about

Formula:

Please Login or Register  to view this content.

Or

Please Login or Register  to view this content.

Both work well, thank you.

However this one:

Formula:

Please Login or Register  to view this content.

would count empty cells as well. So either the array perfectly fits and we are 100% sure there are no empty cells or, personally I used COUNTBLANK to remove the empty cells if any.

Glad to help & thanks for the feedback.

In that case of empty cells you could use

Please Login or Register  to view this content.

Originally Posted by Fluff13

How about

Formula:

Please Login or Register  to view this content.

With regards to (MATCH(A5:A2000,E5:E8,0)), how can I change this to do an exact match instead and return the relative position of case-sensitive values? In my example "A" and "a" return the same relative position in E5:E8 however I would need "a" to return "N/A" instead.

Thanks.

How about

Formula:

Please Login or Register  to view this content.

I do not understand how the function LAMBDA works exactly (not sure what to put in a and b parameters). I will open a new thread and explain the issue I have. Thanks!

Did it work? If not in what way?

For your original question

counting the number of values that don't match the grades available, please try

Formula:

Please Login or Register  to view this content.

Or if you want to expand range A so yo don't have to adjust it each time please try:

Formula:

Please Login or Register  to view this content.

It needs to be case-sensitive, and the MATCH is a combination of two arrays. Please check my post "case-sensitive with MATCH while using arrays"

Originally Posted by Fluff13

Did it work? If not in what way?

Not sure how to use the LAMBDA function here, with a and b parameters. Please check my post "case-sensitive with MATCH while using arrays" which describe fully the issue. Thanks.

And that issue is totally different from your question here.
Added to which you still haven't answered my question.

Originally Posted by Fluff13

And that issue is totally different from your question here.
Added to which you still haven't answered my question.

It looks like it does not work, or maybe I did not use it appropriately. Please see the attached, sheet #1. Thanks !

When I open that file Excel runs a recovery & removes the formula, so I cannot see what you had.
I have added it for you.

Great ! It works fine. Thank you.

Can you solve the 2nd sheet by any chance? Attached.

Great ! It works fine. Thank you.

Glad to help & thanks for the feedback.

You need to stick y=to your new thread for the other question.

Ok thanks. I will mark this one as SOLVED.