Forum Discussion

mb454316's avatar
mb454316
Copper Contributor
Apr 03, 2024
Solved

Need help building a numbering formula

Okay so I have these two tables. In table 1 I would like the “INPUT” column (column D) to populate the Input numbers differently but I can’t figure out a formula to do what I am intending.   ...
Formulas and Functions
  • OliverScheurich's avatar
    OliverScheurich
    Apr 03, 2024

    mb454316 

    =LET(rng,H3:K23,

    numbering,DROP(

    REDUCE("",SEQUENCE(,COLUMNS(rng)),LAMBDA(a,b,

    LET(

    z,IF(b=1,0,COUNT(CHOOSECOLS(rng,SEQUENCE(,b-1,1,1)))),

    HSTACK(

    a,

    REDUCE("",SEQUENCE(ROWS(rng)),LAMBDA(u,v,

    VSTACK(u,IF(INDEX(rng,v,b)="","",z+COUNT(INDEX(rng,1,b):INDEX(rng,v,b))))))))))

    ,1,1),

    "INPUT "&BYROW(numbering,LAMBDA(r,FILTER(r,r<>""))))

     

    This formula returns the intended result in my sheet. However it only works in Office 365 and Excel for the web.

     

     

    For verification i've applied the formula for range M3:P23 and it returns the expected result.

m_tarler's avatar
m_tarler
Bronze Contributor
Apr 03, 2024

mb454316  In case you don't have Excel 365 you can also do it with old formulas like this:

="INPUT "& 
COUNT(INDEX($H$3:$K$3,MATCH(E3,$H$2:$K$2,0)):INDEX($H3:$K3,MATCH(E3,$H$2:$K$2,0)))+
SUM($G$24:INDEX($G$24:$J$24,MATCH(E3,$H$2:$K$2)))

line 2 will add up all the cells with number from this line up to the top of the column for this MPPT

line 3 will add up the CB Counts for MPPT columns before this MPPT

see attached

BTW I also made a formula for the MPPT column in case you were manually entering that before

Resources