I am using the following function to calculate the sum of specific rows and I’d like to modify it to sum different rows if there’s a value in the cell of a specific row as it’s traversing the sequence.
=LET(duration, SEQUENCE(1,E2,COLUMN(K:K)),
projectedRevenueArray,INDEX($1:$11,{4;5;11},duration),
projectedRevenueRowSum, LAMBDA(x,INDEX(x,1)+INDEX(x,2)-INDEX(x,3)),
actualRevenueArray,INDEX($1:$11,{6;11},duration),
actualRevenueRowSum, LAMBDA(x,INDEX(x,1)-INDEX(x,2)),
arrayToUse, IF(1, projectedRevenueArray,actualRevenueArray),
rowSumToUse, IF(1, projectedRevenueRowSum,actualRevenueRowSum),
BYCOL(arrayToUse,rowSumToUse))
For now I have created an IF statement that is fixed to 1 for testing purposes and if I change the 1 to a 0 then it uses a different array and Sum and works as expected. This function lives in cell K12 and sums rows for a duration of months specified in E2.
If I input a test value in to cell K6 alone, I’d like to use that row in the calculation, rather than rows 4 and 5 for that column – a column denotes a month.
I tried the following but this makes all columns sum row 6 rather than just column K:
arrayToUse, IF(K$6=0, projectedRevenueArray,actualRevenueArray),
rowSumToUse, IF(K$6=0, projectedRevenueRowSum,actualRevenueRowSum),
Here’s an example of the data I am using if that helps:
| Jan-20 | Feb-20 | Mar-20 | Apr-20 | May-20 | Jun-20 | |
|---|---|---|---|---|---|---|
| Active Staff | 4 | 8 | 10 | 10 | 8 | 4 |
| Additonal Staff | ||||||
| Project Revenue | £80,000 | 160,000 | 200,000 | 200,000 | 160,000 | 80,000 |
| Revenue Adjustment | ||||||
| Actual Revenue | £15,000 | |||||
| Salary Costs | £14,000 | £26,208 | £34,292 | £34,292 | £28,875 | £14,167 |
| Software Costs | £0 | £0 | £0 | £0 | £0 | £0 |
| Biz Costs | £37 | £37 | £37 | £37 | £37 | £37 |
| Costs Adjustment | ||||||
| Overall Costs | £14,037 | £26,246 | £34,329 | £34,329 | £28,912 | £14,204 |
| Project Profit | £963 | -26245.74074 | -£34,329.07 | -34329.07407 | -28912.40741 | -14204.07407 |
>Solution :
Lambda is being used incorrectly, you never define(x), so you would need to do:
LAMBDA(x,INDEX(x,1)+INDEX(x,2)-INDEX(x,3))(projectedRevenueArray)
That way x is defined, but LET would be better here:
LET(x,projectedRevenueArray,INDEX(x,1)+INDEX(x,2)-INDEX(x,3))
Then BYCOL is also being misused. VSTACK is the function to use.
Now to the actual question. You will need to pass an array to the if of the same size as the data:
IF(--INDEX($1:$11,6,duration)=0, projectedRevenueArray,actualRevenueArray)
All together we get:
=LET(duration, SEQUENCE(1,E2,COLUMN(K:K)),
projectedRevenueArray,INDEX($1:$11,{4;5;11},duration),
projectedRevenueRowSum, LET(x,projectedRevenueArray,INDEX(x,1)+INDEX(x,2)-INDEX(x,3)),
actualRevenueArray,INDEX($1:$11,{6;11},duration),
actualRevenueRowSum, LET(x,actualRevenueArray,INDEX(x,1)-INDEX(x,2)),
arrayToUse, IF(--INDEX($1:$11,6,duration)=0, projectedRevenueArray,actualRevenueArray),
rowSumToUse, IF(--INDEX($1:$11,6,duration)=0, projectedRevenueRowSum,actualRevenueRowSum),
VSTACK(IFERROR(arrayToUse,0),rowSumToUse))
To return just the sum line use:
=LET(duration, SEQUENCE(1,E2,COLUMN(K:K)),
projectedRevenueArray,INDEX($1:$11,{4;5;11},duration),
projectedRevenueRowSum, LET(x,projectedRevenueArray,INDEX(x,1)+INDEX(x,2)-INDEX(x,3)),
actualRevenueArray,INDEX($1:$11,{6;11},duration),
actualRevenueRowSum, LET(x,actualRevenueArray,INDEX(x,1)-INDEX(x,2)),
IF(--INDEX($1:$11,6,duration)=0, projectedRevenueRowSum,actualRevenueRowSum))
