Hi ,
The formula in use in the workbook is :
=SUMPRODUCT((A2:A5=A3)*(B1:F1<=B1)*(B1:F1>=E1)*(B2:F5))
What this does is :
1. It checks for which cell in the range A2:A5 equals the contents of cell A3.
In this case , the only cell which matches is A3 itself.
However , since the SUMPRODUCT function has been used , what is returned is an array {FALSE;TRUE;FALSE;FALSE}.
2. Next , we look at the horizontal array of values in the range B1:F1 , and see which of them is less than or equal to B1. Since the dates in this range are increasing from the start date of 18-Feb-2018 in cell B1 , only that one cell value is less than or equal to the date in B1.
Thus , the return array from this evaluation will be :
{TRUE,FALSE,FALSE,FALSE,FALSE}
3. Next , we look at the horizontal array of values in the range B1:F1 , and see which of them is greater than or equal to E1. Since the dates in this range are increasing from the start date of 18-Feb-2018 in cell B1 , only the two cells E1 and F1 will have values greater than or equal to the date in E1.
Thus , the return array from this evaluation will be :
{FALSE,FALSE,FALSE,TRUE,TRUE}
If we try to understand the logic of these two evaluations 2 and 3 , we are asking Excel to return a set of dates in the range B1:F1 which are less than or equal to the date in B1 , and at the same time , are greater than or equal to the date in E1.
Clearly from the dates in the range , this is impossible.
This is the reason the formula returns 0.
What you want is that Excel returns a set of dates in the range B1:F1 which are greater than or equal to the date in B1 , and at the same time , which are less than or equal to the date in E1. This will then return the dates in B1 , C1 , D1 and E1.
Thus , the revised formula will be :
=SUMPRODUCT((A2:A5=A3)*(B1:F1>=B1)*(B1:F1<=E1)*(B2:F5))
where the changes have been highlighted in red.
Narayan
