Count extra values with name lookup

[TPayne]

Hi,

I am trying to add something to my student markbook. I teach Maths and we use some software in which I set students specific tasks, however they can do more if they wish. I can download data from the software easily and copy it into a sheet in my markbook. My markbook tracks the tasks I set perfectly well, but I want to count and give credit for any extras they do. To try and replicate I've simplified the data to this:

So the top table is like the data I download from the external software. The bottom table is part of my markbook usually in a separate sheet. It is the formula for E9 that I need help with. Imagine I have set two tasks 'yellow and orange'. Tom has done these and scored 50 and 80 respectively. I can grab that information from the data downloaded from the external software no problem. However, Tom has also done two other tasks 'red and blue'. I want the formula in E9 to count any additional tasks that have been attempted that I did not set (so are not in the range C8:D8).

A friend has tried helping and suggested this:

=SUMPRODUCT((--(ISNA(MATCH(C2:F2,C8:D8,0))))*(C3:F3>0))

This formula does count additional tasks completed, but it does not compare the name to the list as well, which I do really need.

Any solutions would be very welcome. I've been puzzling on this for a while..

Thanks
Tom

 

[Fluff]

Hi & welcome to MrExcel.
How about

=SUMPRODUCT(($B$3:$B$6=B9)*(ISNA(MATCH($C$2:$F$2,$C$8:$D$8,0)))*($C$3:$F$6>0))