Good day, I trust someone has bumped into this kind of exercise before and would be able to assist in solving my problem.
I have the following example data set:
<tbody>
</tbody>
I have the following example data set:
<tbody> </tbody> | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
I need to sum Column B on the condition that the dates in column A are greater than "Year Start" cell and less than or equal to "Year End" cell value. Once this has been done, I will count the number of days in the same range between "Year Start" and "Year End" to calculate the average exchange rate for the specified date range. The re are a few problems here that does not make sense to me currently: 1) The formula in Column E does not automatically update and point to the next row when copied down, as per normal drag-and-drop Excel functionality. 2) The greater than and equal to(>=) as well as the less than (<) logic in the function refuses to apply when using it in the SUMIFS function. Formulas/functions used: In the value column I have tried the following formulas to try and sum the valid range as per the logic I need to apply: 1) =SUMIFS($B$8:$B$20,$A$8:$A$20,">="&DATE(E7,E7,E7), $A$7:$A$20,"<"&DATE(F7,F7,F7)) Result = 0 Logically not correct 2) =SUMIFS($B$7:$B$20,$A$7:$A$20,"=datevalue(E8)", $A$7:$A$20,">datevalue(f8)") Result = 0 Logically not correct 3) =SUMIF($A$7:$A$20,E9,$B$7:$B$20) Result = R11.62, which is correct 4) =SUMIF($A$7:$A$20,"<e10",$b$7:$b$20) Result = R151.23 which is correct5) =SUMIF($A$7:$A$20,">=E11",$B$7:$B$20) Result = 0 Logically not correct Please help. It will be greatly appreciated. Andre </e10",$b$7:$b$20) | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
<tbody>
</tbody>