Editing Formula to not include a row- but maintaining everything else.

Aimee Peyton-Greene

I have this formula:

=SUMIFS(CHILDREN(), CHILDREN($[Recruiter Name]@row), <>"PF External PRN") + ROUNDDOWN((SUMIF(CHILDREN($[Recruiter Name]@row), "PF External PRN", CHILDREN([2020-01]@row)) * 0.5))

I need to not count a row in the child rows called "Lawson Starts" while maintaining everything above as well. How would I do this? I tried listing it out separately and the formula ended up not rounding down the PF EXTERNAL ORN that I needed rounded down.

Thank you!

Mark Cronk

Hi Amiee,

Perfect. Appreciate the screen shots.

Try placing this in the parent rows of the column with counts:

=SUMIFS(CHILDREN(), CHILDREN([Recruiter Name]@row), AND(@cell<>"PF External PRN", @cell<>"Lawson Starts")) + ROUNDDOWN(SUMIF(CHILDREN([Recruiter Name]@row), "PF External PRN", CHILDREN()) * 0.5)

Mark

Mark Cronk

Hi Aimee,

Try

=SUMIFS(CHILDREN(), CHILDREN($[Recruiter Name]@row), <>"PF External PRN", children(), <>"Lawson Starts") + ROUNDDOWN((SUMIF(CHILDREN($[Recruiter Name]@row), "PF External PRN", CHILDREN([2020-01]@row)) * 0.5))

If Lawson Starts isn't in the formula colum then you'll need to reference the right column : children([recruiter Name]@row), <>"Lawson Starts"

Is that what you need?

Mark

Aimee Peyton-Greene

Hello Mark!

Thank you for your response, the formula ended up removing the count for the PRN being 0.5 and only counted that Lawson row. My sheets hate me.

Mark Cronk

Good morning @Aimee Peyton-Greene ,

We'll figure it out, or one of the more knowledgeable people will step and and teach us both.

Let's break your formula apart and see if that helps lead to the answer:

The syntax for SUMIFS is =SUMIFS( range, criterion_range1, criterion1, [ criterion_range2​, criterion2​... ])

RANGE | =SUMIFS(CHILDREN(), : you are summing the numbers in the child rows below this row. The children in this column need to be numbers.

Criterion Range 1 | CHILDREN($[Recruiter Name]@row),

Criterion 1 | <>"PF External PRN", : You're only summing the children when the recruiter name at that row is not equal to "PF External PRN".

Criteria Range 2 | children(), -> This is likely your problem. The children have to be numbers to sum but your criteria is text.

Criterion 2 | <>"Lawson Starts") : You'll only sum children that are not equal to "Lawson Starts"

NEXT FORMULA | + ROUNDDOWN(( : You're adding the rounded down number to your 1st formula result. Syntax is ROUNDDOWN( number, [ decimal ]). Your formula doesn't have the [Decimal} component

Your next formula is SUMIF vs SUMIFS. The SUMIF syntax is SUMIF( range, criterion, [ sum_range ])

Your formula:

RANGE | SUMIF(CHILDREN($[Recruiter Name]@row), : You're going to examine the values in the Recruiter Name column under the parent row.

CRITERION | "PF External PRN", You're going to sum numbers where the Recruiter name is "PF External PRN"

SUM_RANGE | CHILDREN([2020-01]@row)) : You're going to sum the numbers in the [2020-01] column of the child rows where the recruiter name is "PF External PRN". [2020-01] needs to contain numbers.

This is where your Round down decimal needs to be after a comma (,). You used * 0.5)). Replace that with a number -4 to 4 to get the number of decimal you're looking for. You probably want ", 1))"

Cross your fingers. Does it work now?

Mark

Aimee Peyton-Greene

Sorry it did not add in the PRN row as needed. Apologies about my lapse in response. I had to shift priorities for a little bit.

Mark Cronk

Hi Aimee, Can you attach a couple screen shots that show your columns and the formula that isn't working? Include enough rows to show your problem. Remove any sensitive data. Happy to help, just need some additional information.

Mark

Aimee Peyton-Greene

Certainly Mark,

First image is the recruiter name, and the child rows that fall below. The second includes the mocked up data for this demo only, the third includes all of it. Based on the data the total (top box) next to Cat's name should read 7 (1 for the External Fills, 1 for the two PRN counting at .5, 6 for the transfers and subtracting one for the 90 day fall offs. I tried changing the data to be a more obvious number and it seems like the PRN row is the only one being counted.

Mark Cronk

Hi Amiee,

Perfect. Appreciate the screen shots.

Try placing this in the parent rows of the column with counts:

=SUMIFS(CHILDREN(), CHILDREN([Recruiter Name]@row), AND(@cell<>"PF External PRN", @cell<>"Lawson Starts")) + ROUNDDOWN(SUMIF(CHILDREN([Recruiter Name]@row), "PF External PRN", CHILDREN()) * 0.5)

Mark

Aimee Peyton-Greene

It seems to be working now! Thank you so much! I am testing it out now!

Mark Cronk

Excellent. Happy to help anytime. Just mention me in your post. Often sreenshots can make it a lot easier to figure out what you need.

Mark