ByCol conditional Lambda in excel-CodePudding

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

CodePudding user response：

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))