I'm trying to develop a formula for cells B2:G2 that will look at the Fiscal Year in cells B1:G1 and sum the information in B8:B31 based on the start and end dates in cells F8:f11. I've tried sumifs, sumproduct, etc. but haven't been able to get anything to work yet. I'm completely stuck. Does anyone have any suggestions on formulas or a better way to lay out the sheet? The answer which should appear if correctly calculated is shown in cells B2 and C2 for reference.
0
votes
1 Answers
1
votes
Put any of these in B2 and copy across:
Non CSE Array formulas:
=SUMPRODUCT((DATE(B$1-1,7,1)<=$A$7:$A$31)*(DATE(B$1,6,30)>=$A$7:$A$31)*$B$7:$B$31)
Or:
=SUM(INDEX((DATE(B$1-1,7,1)<=$A$7:$A$31)*(DATE(B$1,6,30)>=$A$7:$A$31)*$B$7:$B$31,))
Ctrl-Shift-Enter Formula:
=SUM(IF((DATE(B$1-1,7,1)<=$A$7:$A$31)*(DATE(B$1,6,30)>=$A$7:$A$31),$B$7:$B$31))
This needs to be confirmed with Ctrl-Shift-Enter when exiting edit mode. If done properly Excel will put {} around the formula.
The above are all array formulas, When using array formulas the calculations are exponential so the references should not be full column or extend much past the dataset.
In order to do that dynamically we use And INDEX/MATCH to find and set the last row with data INDEX($A:$A,MATCH(1E+99,$A:$A))
So to refer to everything that has data from A7 to the last cell with data we would set the range like this: $A$7:INDEX($A:$A,MATCH(1E+99,$A:$A)) In this case that would be $A$7:$A$31.
So the above three formulas are such:
=SUMPRODUCT((DATE(B$1-1,7,1)<=$A$7:INDEX($A:$A,MATCH(1E+99,$A:$A)))*(DATE(B$1,6,30)>=$A$7:INDEX($A:$A,MATCH(1E+99,$A:$A)))*$B$7:INDEX($B:$B,MATCH(1E+99,$A:$A)))
And
=SUM(INDEX((DATE(B$1-1,7,1)<=$A$7:INDEX($A:$A,MATCH(1E+99,$A:$A)))*(DATE(B$1,6,30)>=$A$7:INDEX($A:$A,MATCH(1E+99,$A:$A)))*$B$7:INDEX($B:$B,MATCH(1E+99,$A:$A)),))
And
=SUM(IF((DATE(B$1-1,7,1)<=$A$7:INDEX($A:$A,MATCH(1E+99,$A:$A)))*(DATE(B$1,6,30)>=$A$7:INDEX($A:$A,MATCH(1E+99,$A:$A))),$B$7:INDEX($B:$B,MATCH(1E+99,$A:$A))))
The last one using Ctrl-Shift-Enter.
Or you can simply use this non array formula, SUMIFS():
=SUMIFS($B$7:$B$1000000,$A$7:$A$1000000,">=" & DATE(B$1-1,7,1),$A$7:$A$1000000,"<=" & DATE(B$1,6,30))
This formula does not have the limitations of the array formula so we can refer to as many rows as we see fit.
