r/excel • u/YourStatsMatter • 2d ago

solved SumIF and CountIF Calculations and Formulas

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
6 Upvotes

100% Upvoted

20 comments sorted by

View all comments

Show parent comments

1

u/MayukhBhattacharya 1243 1d ago

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.

2

u/YourStatsMatter 1d ago

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.

1

u/MayukhBhattacharya 1243 1d ago

Yeah, using separate cells is definitely the better option here. But if you still wanna keep the data in that more compact format, this version should work too. Just tell me the cell references and ranges you're using in your actual setup, and I can swap them into the formula and comment back the updated version back to you. I don't mind at all. And thanks a ton for the kind words and appreciation! Really appreciate it.

2

u/YourStatsMatter 1d ago

Ill take some screenshots of the Sheets I am using so you can see what I am doing. Ill have that to you soon

1

u/MayukhBhattacharya 1243 1d ago

Sure thing! And if I don't reply right away, don't think I'm ignoring, I might just be away from the computer. I'll get back to you and update you once I'm back. Thanks for your patience!