Sumproduct with table references error

[Orfevre]

I am trying to perform a sumproduct with table references and when im trying to add a extra condition it will no longer work. Below are the two examples there must be something simple I am missing the @Type field is just matching text, if anyone could please guide me with the correct syntax to use and when I recreated without the table references in a new workbook it worked fine.

Formula that works

=SUMPRODUCT(--([@Key]=RecFY!AK:AK),--([@[Appointment_Date]]=RecFY!Y:Y),RecFY!AD:AD)

Formula that errors as #N/A have checked there should be a result

=SUMPRODUCT(--([@Key]=RecFY!AK:AK),--([@[Appointment_Date]]=RecFY!Y:Y),--([@Type]=RecFY!V:V),RecFY!AD:AD)

 

[Peter_SSs]

We cannot see your data but do you happen to have any #N/A error values in column V of sheet 'RecFY'

 

[Orfevre]

We cannot see your data but do you happen to have any #N/A error values in column V of sheet 'RecFY'

No errors in the column and a sumifs works fine so im very confused.

 

[Peter_SSs]

No errors in the column and a sumifs works fine so im very confused.

Well, I think it is something to do with your data as the formula works with my sample data.

In a copy of your workbook, can you try reducing the data in 'RecFy' by deleting row sections to see if/when the error goes away? That may help your narrow down and find the offending data.

If the problem persists and you still cannot identify it, can you post a small set of the data that causes the issue (disguise any sensitive data) and post that with XL2BB? You can hide irrelevant columns before producing the XL2BB sample.

Also post a section of the table the formula goes in. Again hide columns other than those that appear in the formulas and the one the formula goes in.