Conditional SUMPRODUCT with blanks on different tab

Hi all,

I have a column with some dates in it. I'm trying to count/sum how many dates are in October 2017, November 2017 etc. I've managed to find a formula that works but it returns a value error if it's on a different tab AND contains blanks. If there are blanks on the same tab it still seems to work, very strange to me! I've tried the following on a simple spreadsheet to try and get it to work:

=SUMPRODUCT((MONTH(Sheet1!E2:E7)=10)*(YEAR(Sheet1!E2:E7)=2017),(Sheet1!E2:E7))
=SUMPRODUCT((MONTH(Sheet1!E2:E7)=10)*(YEAR(Sheet1!E2:E7)=2017),--(Sheet1!E2:E7<>""))

But I just get a value error.

I'm trying to count/sum on a different tab so my final formula will be something like (if possible):
=SUMPRODUCT((MONTH('[Trial Database Final.xlsm]S4 Database Linked'!X3:X5000)=10)*(YEAR('[Trial Database Final.xlsm]S4 Database Linked'!X3:X5000)=2017))

Would very much appreciate help with this. What am I doing wrong?
Many thanks.

Can you not use COUNTIFS instead if you want to count the number of instances?

Please Login or Register  to view this content.

Thank you both so much! Worked a treat.

You are welcome

If that takes care of your question, please mark this thread as Solved by selecting Thread Tools --> Mark thread as solved.