Sumproduct function

[scooter155]

I have a column (A) with dates formatted this way "Tuesday, September 04, 2018".
I also have a column (B) with corresponding amounts in it.
I am trying to use Sumproduct to add all the amounts in column B by month using this formula =SUMPRODUCT((MONTH($A$3:$A$3238)=$C3210)*($B$3:$B$3238))

C3210-C3221 equals the numbers 1-12 to correspond with January-December.
As soon as I extend the formula to go further than row 3238, I get a value error. It works fine as long as I am adding all the data prior to that. There is no data in those rows after 3238 but there will be in the future. I don't want to have to change the formula every time I add data.

I am also adding by year and seem to have no problem using this formula =SUMIFS($B:$B,$A:$A,">=January 1, 2018",$A:$A,"<=December 31, 2018")


Any help would be greatly appreciated.

 

[RoryA]

You shouldn't get a #VALUE ! error with that formula unless there are text cells in column A of the 'unused' data rows. Do you perhaps have formulas returning "" in those cells? If so, you might use:

=SUMPRODUCT(IFERROR(INDEX(MONTH($A$3:$A$3238)=$C2,0),0)*($B$3:$B$3238))

 

[scooter155]

There is no data or formulas in the unused cells, that is what has me stumped.

 

[RoryA]

Did you try the formula I added? (you might not have seen it as I edited the post to add it after originally posting)

 

[RoryA]

Having looked at your workbook, you have a space in cell B3238 which is what causes the error.

 

[scooter155]

I did try the formula, it returned values for everything in Column B, not just specific month.

 

[scooter155]

and there you have it, knew it was something simple, thank you for your time and help