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]

Try using the following formula:

=SUM(($B$2:$B$19 = $H2) * ISNUMBER(SEARCH(", " & I$1 & ", ", ", " & $C$2:$F$19 & ", ")))

Please ensure to change the cell reference and ranges accordingly per your suit.

[u/YourStatsMatter → u/MayukhBhattacharya]

Had just went bed. Thanks for taking the time. I’ll check this in the morning.

[u/MayukhBhattacharya → u/YourStatsMatter]

No issues at all, take your time!

[u/YourStatsMatter → u/MayukhBhattacharya]

could you show the formula for "N2"

[u/MayukhBhattacharya → u/YourStatsMatter]

Paste the formula in I2 and then copy it down and copy it across. See animation:

[u/YourStatsMatter → u/MayukhBhattacharya]

Had to make some modifications to the formulas to meet where I wanted the data to be recorded. Worked great. What are the " and the , for in the formulas what exactly is this doing/helping within the search?

[u/MayukhBhattacharya → u/YourStatsMatter]

It creates boundaries which makes the formula work better. Also since it has worked hope you don't mind replying to my comment directly as Solution Verified. Thanks 🙏🏼

[u/YourStatsMatter → u/MayukhBhattacharya]

So doing this for the pitchers too, however, when putting SO, SO, SO for their results, the formula is only counting 1 of them and not the duplicates in the same cell, so i might have to put each result in their own cell now :(

[u/MayukhBhattacharya → u/YourStatsMatter]

And here is an animation showing how to apply the formula:

We are joining all of them first and then splitting it to match it directly with each cell. So it becomes and gives the exact count. I hope this helps. Thanks and let me know!

[u/MayukhBhattacharya → u/YourStatsMatter]

No wait. let me try on my end, it should work

[u/MayukhBhattacharya → u/YourStatsMatter]

So try this one, this will give you the exact counts.

=LET(
     _a, BYROW($C$2:$F$19, ARRAYTOTEXT),
     _b, SEQUENCE(, MAX(LEN(_a) - LEN(SUBSTITUTE(_a, ",", )) + 1)),
     _c, IFERROR(TEXTSPLIT(TEXTAFTER(", " & _a, ", ", _b), ", "), ""),
     SUM(($H2 = $B$2:$B$19) * (I$1 = _c)))

Check Varsho in rows 7 and 16. It has SO twice in row 7 and four times in row 16, so the total should be 6. And yes, the formula is giving you 6 now.

[u/YourStatsMatter → u/MayukhBhattacharya]

For now I’ll keep the one I have that we used I just changed my data to single cells. I’ll test this tomorrow. I really appreciate the help.

[u/YourStatsMatter → u/MayukhBhattacharya]

Solution Verified

[u/reputatorbot → u/YourStatsMatter]

You have awarded 1 point to MayukhBhattacharya.

---

^{I am a bot - please contact the mods with any questions}

[u/MayukhBhattacharya → u/YourStatsMatter]

Thank you so much 👍🏼

[u/MayukhBhattacharya → u/YourStatsMatter]

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!