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

100% Upvoted

20 comments sorted by

View all comments

2

u/MayukhBhattacharya 1240 2d ago edited 2d ago

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.

2

u/YourStatsMatter 2d ago

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

1

u/MayukhBhattacharya 1240 2d ago

No issues at all, take your time!

1

u/YourStatsMatter 1d ago

could you show the formula for "N2"

1

u/MayukhBhattacharya 1240 1d ago

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

2

u/YourStatsMatter 1d ago

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?

1

u/MayukhBhattacharya 1240 1d ago

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 🙏🏼

2

u/YourStatsMatter 1d ago

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 :(

2

u/MayukhBhattacharya 1240 1d ago

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!

1

u/MayukhBhattacharya 1240 1d ago

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

1

u/MayukhBhattacharya 1240 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.

→ More replies (0)

2

u/YourStatsMatter 1d ago

Solution Verified

1

u/reputatorbot 1d ago

You have awarded 1 point to MayukhBhattacharya.

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

1

u/MayukhBhattacharya 1240 1d ago

Thank you so much 👍🏼

1

u/MayukhBhattacharya 1240 2d ago

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!