Excel: Dynamic Range Date used in other fields: Sumproduct
I am using sumproduct formula to get the first four month, then the second four month, third four month of net sales until one month before today. This is my formula that I used:
=IFERROR(SUMPRODUCT($B3:$Y3*(COLUMN($B3:$Y3)>=AGGREGATE(15,6,COLUMN($B3:$Y3)/($B3:$Y3<>0),1)+4*(COLUMNS(B3)-1))*(COLUMN($B3:$Y3)<AGGREGATE(15,6,COLUMN($B3:$Y3)/($B3:$Y3<>0),1)+4*(COLUMNS(B3)))*($B$1:$Y$1<EOMONTH(TODAY(),-1)+1)),0)
However, I need to capture the same range as I have it for the net sales as for other measures like COGS in my example. I cannot use the formula above for the other measures like COGS as sometimes they are zero in the same range as in the Net Sales.But I need to capture the zeros here as well.
Net Sales
| Jan | Feb | Mar | Apr | May | June | July | Aug | Sept | Oct | Nov | Dec |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 0 | 2 | 3 | 4 | 5 | 2 | 3 | 2 | 3 | 2 | 4 |
---> 1st period= 14 2nd period= 10
COGS (follows the same date range as Net Sales)
| Jan | Feb | Mar | Apr | May | June | July | Aug | Sept | Oct | Nov | Dec |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 0 | 0 | 0 | 0 | 2 | 1 | 4 | 2 | 3 | 2 | 4 |
---> 1st period= 2 2nd Period= 11
You can leave the entire range check logic from the first formula and change just the value range, i.e first formula in my sample:
=IFERROR(SUMPRODUCT($A3:$L3*(COLUMN($A3:$L3)>=AGGREGATE(15,6,COLUMN($A3:$L3)/($A3:$L3<>0),1)+4*(COLUMN(A3)-1))*(COLUMN($A3:$L3)<AGGREGATE(15,6,COLUMN($A3:$L3)/($A3:$L3<>0),1)+4*(COLUMN(A3)))*($A$2:$L$2<EOMONTH(TODAY(),-1)+1)),0)
second formula for COGS:
=IFERROR(SUMPRODUCT($O3:$Z3*(COLUMN($A3:$L3)>=AGGREGATE(15,6,COLUMN($A3:$L3)/($A3:$L3<>0),1)+4*(COLUMN(A3)-1))*(COLUMN($A3:$L3)<AGGREGATE(15,6,COLUMN($A3:$L3)/($A3:$L3<>0),1)+4*(COLUMN(A3)))*($A$2:$L$2<EOMONTH(TODAY(),-1)+1)),0)
