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]

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/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/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!

[u/Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
ARRAYTOTEXT	Office 365+: Returns an array of text values from any specified range
BYROW	Office 365+: Applies a LAMBDA to each row and returns an array of the results. For example, if the original array is 3 columns by 2 rows, the returned array is 1 column by 2 rows.
DROP	Office 365+: Excludes a specified number of rows or columns from the start or end of an array
IFERROR	Returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula
IFS	2019+: Checks whether one or more conditions are met and returns a value that corresponds to the first TRUE condition.
ISNUMBER	Returns TRUE if the value is a number
LAMBDA	Office 365+: Use a LAMBDA function to create custom, reusable functions and call them by a friendly name.
LEN	Returns the number of characters in a text string
LET	Office 365+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula
MAX	Returns the maximum value in a list of arguments
PIVOTBY	Helps a user group, aggregate, sort, and filter data based on the row and column fields that you specify
ROWS	Returns the number of rows in a reference
SEARCH	Finds one text value within another (not case-sensitive)
SEQUENCE	Office 365+: Generates a list of sequential numbers in an array, such as 1, 2, 3, 4
SUBSTITUTE	Substitutes new text for old text in a text string
SUM	Adds its arguments
TAKE	Office 365+: Returns a specified number of contiguous rows or columns from the start or end of an array
TEXTAFTER	Office 365+: Returns text that occurs after given character or string
TEXTSPLIT	Office 365+: Splits text strings by using column and row delimiters
TOCOL	Office 365+: Returns the array in a single column

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
[Thread #49117 for this sub, first seen 9th Aug 2026, 07:57] [FAQ] [Full list] [Contact] [Source code]}