Count extra values with name lookup

TPayne

New Member
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

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Fluff

MrExcel MVP, Moderator
Hi & welcome to MrExcel.
Excel Formula:
``=SUMPRODUCT((\$B\$3:\$B\$6=B9)*(ISNA(MATCH(\$C\$2:\$F\$2,\$C\$8:\$D\$8,0)))*(\$C\$3:\$F\$6>0))``

Replies
4
Views
381
Replies
0
Views
26
Replies
6
Views
40
Replies
11
Views
284
Replies
7
Views
154

1,129,489
Messages
5,636,626
Members
416,932
Latest member
mm07

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back