Strange #VALUE Error

[WillWWBR]

Hi, I'm building a spreadsheet which uses a lot of COUNTIF Formulas. The workbook has one sheet with formulas, one sheet with data for year 1, and one sheet with data for year 2.

When I run a formula based on Year 2, it works great. You will see I'm using Column Names in my formula.
=COUNTIFS(Registration_Type,"Delegate",Sales_Method,"",Job_Function,"*FE501*",Order_Status,"<>Cancelled",Del_Rev___Office_Currency,">0",Provisional?,"N")

However, when I run an equivalent formula on Year 1, I get an error when I add in one of the COUNTIF criteria. Yet running this error prone COUNTIF formula on it's own does not give me an error. i.e. the error only occurs when I nest this formula in the COUNTIF with other criteria.
So this works on it's own:

=COUNTIF(Job_Function_PY,"<>*FE501*")
This works (which excludes the above):

=COUNTIFS(Registration_Type_Prev_Yr,"Delegate",Sales_Method_Prev_Yr,"",Order_Status_Prev_Yr,"<>Cancelled",Del_Rev___Office_Currency_Prev_Yr,">0")
But when I next them together, I get an error. I can't get my head around it.
=COUNTIFS(Registration_Type_Prev_Yr,"Delegate",Sales_Method_Prev_Yr,"",Order_Status_Prev_Yr,"<>Cancelled",Del_Rev___Office_Currency_Prev_Yr,">0",Job_Function_PY,"<>*FE501*")

I've never heard of an error where certain COUNTIF criteria work fine apart but can't be combined. Can anyone help?!

In the attached I've highlighted 4 cells with formula issues like this in yellow. 2 are working (working from data in the Current Orders sheet), 2 aren't (working from data in the prev year Orders sheet)

Thanks

Will

[#2]

@WillWWBR 

The defined names don't have the same size.

Registration_Type_Prev_Yr and Sales_Method_Prev_Yr refer to rows 2:1048576, but Job_Function_PY refers to an entire column (i.e. 1:1048576). Modify Job_Function_PY to refer to rows 2:1048576 too.

[#3]

@Hans Vogelaar this is true and thank-you for spotting! However, that's one of the things I tried adjusting and it didn't fix it sadly. So I don't think it can be the cause :( 

[#4]

@WillWWBR 

It works!

But you also have invalid named ranges:

Workbook attached below.

[#5]

Amazing thank-you so much! How did you fix it out of interest?

Don't worry about those other references. I hadn't got as far as fixing them as I'd lost about 2 hours trying to fix this issue!

[#6]

I just re-read your explanation from before and I understand now. Sorry, I misunderstood what you were saying before or I would have tested myself! Thank-you again!!!