COUNTIF CHILDREN, Numbering Each Child

Tamara

I am looking to create a count formula that counts only the Children and numbers them consecutively. I can then use an embedded IF statement to calculate specific hours attributed to each Child. This simple formula, =COUNTIF(CHILDREN(), 0) outputs the number of Children in the Parent cell, but leaves a "0" in each Child cell.

This is a very large sheet and so I need the formula to run throughout. Essentially the parent row would be blank and the first child row would be one, then two, three, etc.

Any help would be greatly appreciated.

Tamara

Paul Newcome

Can you provide a screenshot that has sensitive/confidential data removed, blocked, and/or replaced with "dummy data" as needed?

Tamara

Hi Paul. Thank you so much for helping me. I’m really stuck.

I'm working in a large sheet and trying to adjust dates, making them more accurate. This is a screen shot of the front of the current sheet, and doesn't show the data columns referenced below, (e.g. MAX QC Date, Start Date Days), but gives you an idea of the scope of Project sheet.

What’s not shown in either example is the “MAX QC Date” and “Start Date Days” columns. The simple formula for “Start Date” is subtract the (largest value in range) MAX QC DATE from the SHIP DATE, then minus the number of Start Date Days.  

The issue I’m struggling with is how to apply this specifically to each Parent/Child Range.  The Project Sheet has hundreds of Parents, each with varying amounts of Children.  The Start Date formula needs to apply to each Parent/Child relationship separately, subtracting the (MAX QC Date from the Ship Date) minus the (Start Date Days), for each Child in that range, then start again at the next Parent.

Trying to solve, I’ve identified Parent/Children with 1 and 0, captured the MAX QC DATE OF EACH Parent/Children range, and counted the number of Children. These may not all be relevant to the solution.

In the example shown below, the first Parent has a MAX QC DATE of 4/30/2020, and twelve Children. The want to subtract the (MAX QC DATE from the Ship Date) of the 12 Children, then subtract the Start Date Days (which varies for each Child), using the row data of this Parent/Children range, then repeat for next Parent/Children range group.

Can SS know to pick the associated date range details only from the column range connected to each set of Children? Or is there a better way?

I've created a helper column and tried this formula to populate the the Max QC Date in row for each child, but I haven't been able to make it work. I'm thinking, if it worked, when the next Parent is found "1", the logic will reset there (after adding a blank ""),and start again finding the next MAX QC Date for the next Parent/Children set.

=IF([Parent Child Test]@row <1, (MAX(CHILDREN([To QC Date]))@row, "")

I’m hoping I described this well enough. Thank you so much for any help and stay well, Tamara

Paul Newcome

What is the formula you currently have for [Start Date] that needs to be adjusted for each specific parent/child section?

Tamara

Hi Paul,

I’ve figured out most of the formulas I was trying to determine. Basically, my ship date creates a baseline date for all the other dates to function off of. These dates are determined by hours. Where I am super stuck is the hours are added like a running balance. I enter the starting "QC Hours" when creating the job, but am looking for a formula to calculate the cumulative total, so I don't have to. Shown in the images below are examples, in addition to the Excel formula to give you a better idea.

Right now, these numbers calculate from the bottom up. Since my SS formula captures the max ship date, I think it can work similar to a running balance. This formula needs to restart and sum for every Parent/Child set.  

I've tried a lot of variations and can't get it to compute. Thank you so much for your help. Tamara :)

Paul Newcome

Ok. I'm lost. What do you actually need help with? First you wanted to number child rows. When I asked for clarification you asked about adjusting an existing date formula to cover child rows. When I asked for clarification on that, you responded with generating a cumulative total.

What exactly do you need help with?

Tamara

My apologies Paul, I appreciate you trying to help and now that I read my replies I can see why you'd be lost. I have this huge project I've been working on to calculate dates, and quite honestly I am extremely stuck. Here is the scenario.

The page I have never had working parent rows. I've added the parent rows, but am having difficulties getting the formulas to start and stop where they need to. My first thought was how do I create a count if formula so I can number the children in each set. Thinking I could hinge the calculation for the date based on greater than or less than, or make it stop so that way my formula knows when to stop.

There are four sets of dates. Working from the 4th column to the 1st.

The 4th column is the date I input- for the final ship date.

The 3rd column is the final phase of production(QC). The date basically looks at the total hours of QC from the bottom up (2 phases) and determines the max date a project needs to hit this phase.

The 2nd column starts calculating off the same max date and determines the date the project must start phase 1 of production. Based off total cumulative hours and the max date.

The 1st column is the parts order phase. This date is scheduled exactly 14 workdays before column 2.

The two formulas I have that are causing a problem are as follows: ( These are on row 1 of some helper columns I've created.)

Cumulative QC

=IFERROR(IF(COUNT(CHILDREN([PARENT CHILD TEST]@row)) = 0, SUM([QC Hours]@row, [CUMULATIVE QC HOURS]2, IF(COUNT(CHILDREN([PARENT CHILD TEST]@row)) = 1, 0))), "0")

Cumulative Assembly and Wiring

=IFERROR(IF(COUNT(CHILDREN([PARENT CHILD TEST]@row)) = 0, SUM([Total Hours of Assembly and Wiring]@row, [CUMULATIVE ASSEMBLY WIRING HOURS]2, IF(COUNT(CHILDREN([PARENT CHILD TEST]@row)) = 1, 0))), "0")

**The problem is these formulas don't know when to start/stop. If I add a row or delete a row I get hundreds of #BLOCKED messages and have to re-drag the formulas. Everything else works perfect. **

Any help is truly appreciated! My sincerest apologies and Thank You!

Paul Newcome

Ok. Let's try starting from scratch and making sure I understand exactly what you are trying to accomplish. Let's forget about helper columns and formulas and whatnot and make sure I understand the overall logic first.

Child [Ship Date]: Manually entered.

Parent [Ship Date]: MAX of Child [Ship Date] rows.

Child [QC Date]: What logic determines this?

Parent [QC Date]: What logic determines this?

Child [Start Date]: What logic determines this?

Parent [Start Date]: What logic determines this?

Child [Parts Due Date]: What logic determines this?

Parent [Parts Due Date]: What logic determines this?

Tamara

Paul,

I hope this isn't confusing. Thanks for your help!

Child [Ship Date]: Manually entered. Correct

Parent [Ship Date]: MAX of Child [Ship Date] rows. Correct

Child [QC Date]: What logic determines this? The QC Date is determined by taking the MAX “Ship Date”, minus the QC Days, minus the QC Buffer. The logic for the QC Date is determined from adding the QC hours from the bottom up, dividing by 7 and rounding up, adding a 2-day buffer, giving the “Actual QC Days”, shown below in “blue”. 

Parent [QC Date]: What logic determines this? This should be the maximum QC Date.   This uses the WORKDAY function to avoid weekends.

Child [Start Date]: What logic determines this? This date is determined from starting at the max workday’s QC date. Applying the same logic as the QC dates above. Adding the total Assembly and Wiring hours together from the bottom up, then divide by 7 (7-hour work days) and rounded up. This number gives the total Assembly days and is subtracted from the maximum QC date.

Parent [Start Date]: What logic determines this? This is the first workday for starting the project to have it finished on-time. 

Child [Parts Due Date]: What logic determines this? This date is exactly 14 workdays before the Start Date at the row.

Parent [Parts Due Date]: What logic determines this? This is the earliest workday parts need to be received by in order to start the project. 

Thanks again! Tamara

Paul Newcome

Ok. I think I understand it, but I do have a few more questions before I get into testing in my own sheets...

First I want to verify that you are rounding UP in the QC Days column? I ask because the middle row is actually rounded down.

20/7 = 2.857 which should round up to 3, but you have 2 displayed.

Are you open to additional columns? I think I may have an idea on how to automate the summing from the bottom up portion.

When you say:

"minus the QC Days, minus the QC Buffer"

Does that mean you are actually subtracting

10

10

6

4

4

or are you actually just subtracting the blue column?

Tamara

Hi Paul. You are correct, I made a typo, the 2 should be a 3, (2.857 rounded up), and then with the 2-day buffer the 3 becomes a 5. I updated the graphic below.

Adding helper /other columns are fine, any amount I think, unless there is a limit I'm not aware of. I'm subtracting like you show, but the numbers are 6, 6, 5, 3, 3 --> that's the total QC Days, which includes the 2-Day buffer.

The blue column is subtracted from the ship date. For example, if the ship date is 5/15/20, then the QC Dates using the WORKDAY function are: 5/7, 5/7, 5/8, 5/12, and 5/12.

Thanks again!

Paul Newcome

Ok. I think I got it. I am going to do some testing and whatnot and will get back to you, but it won't be until some time next week (my work week is almost over yay).

Tamara

Thank you so much Paul. I understand. Enjoy your time off. I've been reading other SUMIF posts, (many from you), trying to better understand. I'll study more this weekend. Looking forward to your reply. Thanks so much.

Paul Newcome

@Tamara I was able to get something figured out for the "summing from the bottom up" issue. I put it in your other post. I think the rest of the logic is going to be relatively straight forward pulling the appropriate date and subtracting the corresponding days. I'll work on some of that Monday morning and see what we can come up with.