Forum Discussion
Solved
Search for date and then add cells in a column HELP
Please can I ask for a little help finishing this formula. 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 28.
The formula:
=SUMPRODUCT((MONTH(A2:A500)=6)*(YEAR(A2:AA500)=2017)*(SUMIF(B12:B15,"<>#n/a")))
- in column B - eliminate first the #N/A .
if there is formula in B, use this =IFNA(YOUR FORMULA,"")
HTH
9 Replies
- in column B - eliminate first the #N/A .
if there is formula in B, use this =IFNA(YOUR FORMULA,"")
HTHLorenzo thank you for your reply.
Every cell in column B has this consecutive formula, but it's only a lookup to another sheet.
Cell B2 ='057 RD'!CS6
Cell B3 = ='057 RD'!CS7
Cell B4 = ='057 RD'!CS8 and so on.
The easiest solution would be delete the #N/A's from the other sheet, but this brings up a new problem in that the above formulas report a "0" if the cell in the other sheet is blank. I cannot have 0's in this column B.
GOT IT :-)
Thank you so much...
- Also found this online, but again it cannot handle the #N/A's.
=SUMIFS(amount,date,">"&H5,date,"<"&H6)
