Why is this formula giving incorrect data (SUMPRODUCT)?

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Do you have blank cells? MONTH(A1) will return 1 if A1 is blank.

You may use:
=SUMPRODUCT((MONTH('Sheet1'!$E$2:$E$99)=1)*('Sheet1'!$E$2:$E$99<>""))+SUMPRODUCT((MONTH('Sheet2'!$E$2:$E$100)=1)*('Sheet2'!$E$2:$E$100<>""))

I have tested this and it is working. May you provide a sample that shows this error?

Certainly. Your section was:
MONTH('Sheet1'!$E$2:$E$99)=1)
which returns TRUE for all rows where month is 1, but this includes blank cell.
('Sheet1'!$E$2:$E$99<>"")
returns TRUE for cells with some data and FALSE for blank cells
(TRUE is 1 for mathematical purposes in Excel and FALSE is 0)
Multiply these together and we have 1 for each row that month = 1 and cell is not blank (0 for all other rows). Then these are added to give count for the month.

Originally Posted by Izandol

You may use:
=SUMPRODUCT((MONTH('Sheet1'!$E$2:$E$99)=1)*('Sheet1'!$E$2:$E$99<>""))+SUMPRODUCT((MONTH('Sheet2'!$E$2:$E$100)=1)*('Sheet2'!$E$2:$E$100<>""))

If the dates are all within the same year you can check the dates with a single test. For example, to check for dates in Jan 2014...

Instead of using 2 conditional tests like this:

=SUMPRODUCT((MONTH('Sheet1'!$E$2:$E$99)=1)*('Sheet1'!$E$2:$E$99<>""))

We can use 1 conditional test like this:

=SUMPRODUCT(--(TEXT('Sheet1'!$E$2:$E$99,"mmm yyyy")="Jan 2014"))