Issue with Complex formula only when now referencing different sheets. receiving #VALUE!

Hello,

I am having an issue with this formula.

=SUMPRODUCT(($H$2:$H$5000="YES")*(MONTH($G$2:$G$5000)=$K4))

It works perfectly on my excel doc. that is 1 sheet.

However, I am now recieving #VALUE! as a result on my new excel doc. that now has separate sheets and has the Month Value [G], Generated from my separate sheet called "Shipping Report" using this equation

[G]=IF(ISBLANK('SHIPPING REPORT'!H2),"",'SHIPPING REPORT'!H2). Rather than being manually entered on the same sheet as the first equation listed.

What is the issue?

Must I have [G] be manually entered on the same sheet for the first equation to work? is the second equation that it is being generated from creating issues?

Please help! Thanks!
Nate

If it is unclear, let me know. I can explain better or send a worksheet.

here is an example

Tab 1 is correct. tab 2, table is all screwed up [only difference is noted in textbox on tab 2]

I fixed it. For some Reason, If I change it to calculate based off "Due Date" and "Yes/NO" instead of "Date completed" and Yes/No" it works....