SUMPRODUCT with COUNTIF not working with named range

[unionfind]

I have the two example sheets below:


sheet2.JPG

sheet3.PNG

(The example table above is highly simplified for this question)

I'm trying to write a formula that adds the total occurrences of A under the year 2017 (Answer is 3)

This formula works if I add two COUNTIF together for each sheet:

Please Login or Register  to view this content.

I tried to combine them into a single formula below. However I'm getting a #Value error. "Sheets" is a named range consisting of {Sheet2, Sheet3}.

Please Login or Register  to view this content.

Why is the above formula giving me an error when I try to use the named range?

I have attached the example excel file. Appreciate any help.

[unionfind]

Anyone know if this is possible using a formula?