Sumifs formula giving #value even though each part individually works as a sum formula

I need to calculate in sheet 2 based on criteria in sheet1. Each part of the sumifs formula works on its own as a sum formula but as soon as I try it as a sumifs formula it will not work. Please can you tell me what I am doing wrong.

Formula in C12 of sheet 2: =SUMIFS(Sheet1!$F$4:$H$999,Country,Sheet2!A12,Certified_Type,Sheet2!B12,SaleDate,Sheet2!$O$1,Certificate_of_Origin,1)

I have checked that the ranges are all equivalent size, and have added a formula to change the date from datetime to date only in column AA of sheet 1.
columns AA,AB, AC & AD in sheet 1 also contain formulas so I am hoping that this will not be the problem
Column AB formula is: =IF(OR(SUM(F4:H4)>0,SUM(O4:Q4)>0),1,"")

Please see attached example workbook

Thank you in advance

Originally Posted by carrach

I have checked that the ranges are all equivalent size.

But they're not....

The SumRange (Sheet1!$F$4:$H$999) is 3 columns
But each of the named ranges are only 1 column each.

Why is the sum range covering 3 columns anyway? Change that to just
Sheet1!$F$4:$F$999

Jommo1 - because I need it to sum all 3 columns as 1 total - is that not possible?
Oeldere - sorry that is because my original file is called export docs matrix check 1 and it has copied across into the example sheet.

That worked brilliantly. thank you so much. Hadn't realised that the sumrange had to be 1 column wide as well so thank you for that clarification

Glad to help, thanks for the feedback.