Need help to resolve Excel Formula using Let,Take,index,Sort,Unique,HSTACK,MMULT,TOROW,MONTH.

Norman_McIlwaine 

Perhaps the easiest way is to define names using Name Box (on top left of the grid). Stay on the cell which you'd like to name, type in the box the name, Enter

Same for the range - select it in grid and enter the name.

If you'd like to edit name reference, that's Formula->Name Manager, here stay on the bottom bar, press F2 and edit here

Formula in 'With ID' sheet is with such names, internal ones are removed.

Didn't catch about new lines, do you mean multiline text within the cell or something else?

SergeiBaklan 

Serge I placed the year, monthID, monthNumber as definitions, when I asked to run in FORMULAS > RUN IN FORMULAS I get the error message:

How can I run the definitions in the formula?

Thanks for your help.

Norman_McIlwaine 

Now just remove these two in formula

Please be sure the range is correct. In initial formula it was B2:G300, now you defined it as csv62719!A2:H287

Run in Formulas was not necessary.

I have cleaned up the definitions and when I execute the formula i get a #VALUE in the "group" definition. I have looked at this field several times and cannot see why it would give an error. Can you have a look at it for me Thanks.

Norman_McIlwaine 

Please do not define here internal names. Only what is in greed - range, month, and monthId. Nothing else. Entire formula with that will be

=LET(
    date, CHOOSECOLS(range, 2),
    rangeId, CHOOSECOLS(range, 1),
    monthRange, FILTER(range,
        (EOMONTH(date,0) = EOMONTH( DATE(year,monthNumber,1), 0 ))*
        IF( monthID = "", 1, ( rangeId = monthID ) )
    ),
    Id, CHOOSECOLS(monthRange, 1),
    transaction, CHOOSECOLS(monthRange, 4),
    type, CHOOSECOLS(monthRange, 5),
    amount, CHOOSECOLS(monthRange, 6),
    groupIndexes, {1,4,5},
    group, UNIQUE( CHOOSECOLS(monthRange, groupIndexes ) ),
    result,VSTACK({"Id","Transaction","Type","Amount"},
    HSTACK(
        group,
        BYROW( group,
            LAMBDA(v, SUM(
                FILTER(amount,
                    (Id = INDEX(v,1,1) )*
                    (transaction = INDEX(v,1,2) )*
                    (type = INDEX(v,1,3) )
                )
            ) )
        )
    ) ), result
)

Norman_McIlwaine 

Problem solved incorrect placing of a comma and result. All fixed now looking good. You are a wonderful person for helping me on this project.

Norman_McIlwaine , great to know, thank you for the feedback

Norman_McIlwaine 

Just a quick question, Why do I have a blue box around all the cells from the output and cannot transfer the values from one cell in the blue box to another cell outside the blue box.? I get a blank cell in the TO cell after I copy and paste. Sorry my blue lines are not straight.

I get the blue box as soon as my cursor enters any cell inside the output range. How can I copy the contents of the cell inside the blue box say to another cell location?