Count extra values with name lookup

TPayne

New Member
Joined
Dec 16, 2020
Messages
1
Office Version
  1. 365
Platform
  1. Windows
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:

1608152522184.png


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

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,526
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
Excel Formula:
=SUMPRODUCT(($B$3:$B$6=B9)*(ISNA(MATCH($C$2:$F$2,$C$8:$D$8,0)))*($C$3:$F$6>0))
 

Watch MrExcel Video

Forum statistics

Threads
1,129,479
Messages
5,636,575
Members
416,925
Latest member
malamutus

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top