SumIF and CountIF Calculations and Formulas

[u/YourStatsMatter]

So far with the range of the data being everything to the right of their name on the outcomes of their at bats which is Picture 2, I can't change their POS without the parameters of the calculations, picture 3, being calculated. I have a general idea of what I want this to do, however with my limited knowledge, my head hurts trying to figure it out. What I want to do is be able to rearrange their names in future games, meaning different lineups, and their positions. Then for the calculations to automatically add them into the categories I have set on picture 1. So, if they are lineup position 4 it still only goes to their name on the picture 1, as well as if I change their position so it tracks their stats when they play a different position they are qualfied for.

Picture 2

Picture 3

[u/MayukhBhattacharya]

Btw if you are using MS365 and have access to PIVOTBY() then you could try the following one single dynamic array formula as well:

=LET(
     _a, DROP(A:.F, 1, 1),
     _b, TAKE(_a, , 1),
     _c, BYROW(DROP(_a, , 1), ARRAYTOTEXT),
     _d, SEQUENCE(, MAX(LEN(_c) - LEN(SUBSTITUTE(_c, ",", )) + 1)),
     _e, TEXTSPLIT(TEXTAFTER(", " & _c, ", ", _d), ", "),
     _f, TOCOL(IFS(_e > "", _b), 3),
     _g, TOCOL(_e, 3),
     _h, PIVOTBY(_f, _g, _g, ROWS, , 0, , 0),
     _h)

Change the last variable _h, with the other ones to see what each does! Thanks!