Hi Mark,

I suggest changing your formula completely and adding in a couple search parameters to correctly identify how many hits you have against a certain month within a certain year.

I assume the first date in column A exists in Cell A2.

First, identify 4 blank cells in row 1 and a blank column which you can utilise - for this example let’s use C1, D1, E1, F1 and Column C as the blank column.

In C1, enter in the formula =SUM(C2:C500)
In D1, enter the year you are searching for - i.e. 2017
In E1, enter the month in text you are searching for - i.e. June
In F1, enter in the formula =MONTH(1&LEFT(E1,3)) - this will return the month you have entered in E1 as the month number.

Change your formula in Cell B2 to read =IFERROR((ORIGINAL_FORMULA_IN_CELL_B),"") – this should get rid of the annoying #N/A within cells. Drag down to end of data

Now in column C, beginning at C2, enter in the formula =IF(B2="","",IF(AND(MONTH(A2)=$F$1,YEAR(A2)=$D$1),B2,"")) – This will return the value in column B only if the date in column A meets the requirements of the year and month you have entered into Cell D1 and E1. Drag column C down to the end of data.

Cell C1 will now sum up all values within column C for the desired month and year.

Conversely, if you don’t want an additional column, enter into Cell B2 and drag down the formula:
=IF(IFERROR((ORIGINAL_FORMULA_IN_CELL_B),"")="","",IF(AND(MONTH(A2)=$F$1,YEAR(A2)=$D$1),B2,""))

In B1, enter the formula =SUM(B2:B500), and this will return the sum of all values within column B.

Hope that helps - Marty