0
Undo
Votes
Please can I ask for a little help finishing this formula. It works fine until it bumps into a cell containing #N/A. I have two columns A & B, A is a date range and B contains data which I need to add up for a given date range. In the example I am giving I am asking for the sum of data from June 2017. The answer should be 1, but I am getting is 4.
The formula:
=SUMPRODUCT((MONTH(A2:A500)=6)*(YEAR(A2:AA500)=2017)*(SUMIF(B12:B15,"<>#n/a")))
The formula:
=SUMPRODUCT((MONTH(A2:A500)=6)*(YEAR(A2:AA500)=2017)*(SUMIF(B12:B15,"<>#n/a")))
0
Undo
Votes
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
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
- Page :
- 1
There are no replies made for this post yet.