Using multiple condition summing, combined with logic using date and value ranges as parameters

[RedDragon]

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:

[TABLE="width: 539"]
<tbody>[TR]
[TD="colspan: 4"][TABLE="class: outer_border, width: 50"]
<tbody>[TR]
[TD]1
[/TD]
[TD="colspan: 4"]SA Rand vs US Dollar[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD="colspan: 4"]Weighted average of the banks' daily rates at approximately 10:30 am. Weights are based on the banks' foreign exchange transactions.[/TD]
[TD="colspan: 2"]Variables to be compared to[/TD]
[/TR]
[TR]
[TD="align: center"]4
[/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B [/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]D
[/TD]
[TD="align: center"]E
[/TD]
[TD="align: center"]F
[/TD]
[/TR]
[TR]
[TD="align: center"]5
[/TD]
[TD="align: center"]Date[/TD]
[TD="align: center"]Value[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Value[/TD]
[TD="align: center"]Year Start[/TD]
[TD="align: center"]Year End[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]2015-01-09[/TD]
[TD]11.5898[/TD]
[TD][/TD]
[TD] R -[/TD]
[TD]2015-01-02[/TD]
[TD]2015-01-09[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]2015-01-08[/TD]
[TD]11.6426[/TD]
[TD][/TD]
[TD] R -[/TD]
[TD]2015-01-02[/TD]
[TD]2015-01-09[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]2015-01-07[/TD]
[TD]11.7248[/TD]
[TD][/TD]
[TD] R 11.62[/TD]
[TD]2015-01-02[/TD]
[TD]2015-01-09[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]2015-01-06[/TD]
[TD]11.7264[/TD]
[TD][/TD]
[TD] R 151.23[/TD]
[TD]2015-01-02[/TD]
[TD]2015-01-09[/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD]2015-01-05[/TD]
[TD]11.7071[/TD]
[TD][/TD]
[TD] R 151.23[/TD]
[TD]2015-01-02[/TD]
[TD]2015-01-09[/TD]
[/TR]
[TR]
[TD]12
[/TD]
[TD]2015-01-02[/TD]
[TD]11.6219[/TD]
[TD][/TD]
[TD] R 151.23[/TD]
[TD]2015-01-02[/TD]
[TD]2015-01-09[/TD]
[/TR]
[TR]
[TD]13
[/TD]
[TD]2014-12-31[/TD]
[TD]11.5719[/TD]
[TD][/TD]
[TD] R 151.23[/TD]
[TD]2015-01-02[/TD]
[TD]2015-01-09[/TD]
[/TR]
[TR]
[TD]14
[/TD]
[TD]2014-12-30[/TD]
[TD]11.622[/TD]
[TD][/TD]
[TD] R 151.23[/TD]
[TD]2015-01-02[/TD]
[TD]2015-01-09[/TD]
[/TR]
[TR]
[TD]15
[/TD]
[TD]2014-12-29[/TD]
[TD]11.6049[/TD]
[TD][/TD]
[TD] R 151.23[/TD]
[TD]2015-01-02[/TD]
[TD]2015-01-09[/TD]
[/TR]
[TR]
[TD]16
[/TD]
[TD]2014-12-24[/TD]
[TD]11.6497[/TD]
[TD][/TD]
[TD] R 151.23[/TD]
[TD]2015-01-02[/TD]
[TD]2015-01-09[/TD]
[/TR]
[TR]
[TD]17
[/TD]
[TD]2014-12-23[/TD]
[TD]11.5662[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18
[/TD]
[TD]2014-12-22[/TD]
[TD]11.5905[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19
[/TD]
[TD]2014-12-19[/TD]
[TD]11.6101[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 4"]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)
[/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Andrew Poulsom]

Shouldn't DATE(E7,E7,E7) be just E7? The DATE function's arguments are Year, Month and Day, but you already have a date in E7.

 

[RedDragon]

Hi Andrew,
Thank you for the reply. If you look at the function arguments, you will see that the method used is the correct one as the function extracts the year, month,day and in other related function cases the time as well from the date-time code value. Simply using " DATE(E7)" thus does not work. One has to provide the cell reference for all three in the date function. (Excel 2013)

 

[RedDragon]

My apologies Andrew. I have completely misread your comment. You are correct. My frustration still persists though and it is due to some reason Excel automatically inserts quote marks into the function. The result though from the following still produces a zero result which I fail to understand.

=SUMIFS($B$8:$B$10000,$A$8:$A$20,">=E8", $A$8:$A$20,"<F8")<f8")'
.....

<f8")
Any advice?</f8")
</f8")'

 

[AhoyNC]

One issue I see with the above formula is you don't want the E8 to be in the quotes (I assume E8 is a cell reference).
Change to:

=SUMIFS($B$8:$B$10000,$A$8:$A$20,[COLOR=#0000cd]">="&E8[/COLOR], $A$8:$A$20," .....

 

[Andrew Poulsom]

Instead of ">="&DATE(E7,E7,E7) try ">="&E7.