SUMPRODUCT - between two worksheets and two different array sizes

[Lacaycer]

Sumproduct not working....don't know why. Does anyone see anything obvious here? I want to check when Z6:Z64 matches A5:601 on another worksheet, and an indexed column (A5:O601) has a check mark in it, and G6:G64 is a number.

Sumproduct.xlsx

[Lacaycer]

=SUMPRODUCT((Z6:Z64='Training Requirement Helper'!A5:A601)*(INDEX('Training Requirement Helper'!F4:O601,,MATCH(DynamicMatrix!E3,'Training Requirement Helper'!F4:O4))="√")*(ISNUMBER(E6:F64)))

This is what I have so far. The first portion returns #N/A but I don't understand why... all the values is the Z6:Z64 range exist in the A5:A601 range.

[daddylonglegs]

The first portion returns #N/A but I don't understand why...

Well, specifically because you can't compare a 59 cell range against a 597 cell range. Do you want to check whether Z6:Z64 has a match anywhere in 'Training Requirement Helper'!A5:A601? If so you could use ISNUMBER/MATCH like this

=ISNUMBER(MATCH(Z6:Z64,'Training Requirement Helper'!A5:A601,0))

That will give you TRUE/FALSE for each value in Z6:Z64 depending on whether it exists in the other range or not

.....but that still won't make the formula work, the INDEX part is returning a 598 cell column and the last part (ISNUMBER) is back to 59. THose numbers all have to be the same.....

I don't understand the logic of the INDEX part, can you explain again in words what you need the formula to do?

[Lacaycer]

I have a database where documents are listed from A5:A???
Operator codes are listed from F4:O4
the grid below contains check marks to signal that that document needs to be trained for the operator code

On my Dynamic Matrix worksheet, there are two employee codes (E3/F3). Below are the dates that an employee was trained on. I need to know how many of of those dates were for training that was required.... or had a check mark in the index between the document and operator code on the other sheet

[Lacaycer]

Maybe sumproduct isn't the way to extract the information I need...but I couldn't figure out how to make a countifs do what I need done either.