COUNTIF that will only count child rows when present

Travis Myers
Travis Myers ✭✭
edited 01/11/21 in Formulas and Functions

This is another slight spin off of another question I had asked.

How would I structure the COUNTIFS formula to ignore the parent row if child rows are present?

The main formula I am using is:

=COUNTIFS({FOLLOW UP Range 2}, AND(@cell > DATE(2021, 1, 1), @cell < DATE(2021, 1, 31)), {Follow Up Contractors}, FIND("ACCURATE", @cell) > 0)

Which works just fine (sorta). The problem lies mostly in how our data is laid out. In this particular instance, the word "ACCURATE" appears in both the parent AND child record, but I do NOT want to count it where it appears in the parent. Later it might appear in a row that does not have any child records, I would want to count that instance.


What do I need to modify to get this to do that? Or is it not quite possible? I would also like to clarify that the columns data type is dropdown single select (although we type things in there from time to time).

Tags:

Best Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    Hey

    Try this

    Add a checkbox helper column to source sheet - let's call it Parent

    =IF(Count(Children(Primary@row)>0, 1). This will flag all rows with children

    In your CountIfs statement, exclude the checked rows.

    =COUNTIFS({FOLLOW UP Range 2}, AND(@cell > DATE(2021, 1, 1), @cell < DATE(2021, 1, 31)), {Follow Up Contractors}, FIND("ACCURATE", @cell) > 0, {Followup Parent}, 0)

    Be sure to change the range and column names to match your sheets

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    If your target sheet has the word you're looking for, you can use the syntax

    COUNTIFS({Range of source sheet}, FIND([target sheet column name]@row, @cell) > 0)

«1

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    Hey

    Try this

    Add a checkbox helper column to source sheet - let's call it Parent

    =IF(Count(Children(Primary@row)>0, 1). This will flag all rows with children

    In your CountIfs statement, exclude the checked rows.

    =COUNTIFS({FOLLOW UP Range 2}, AND(@cell > DATE(2021, 1, 1), @cell < DATE(2021, 1, 31)), {Follow Up Contractors}, FIND("ACCURATE", @cell) > 0, {Followup Parent}, 0)

    Be sure to change the range and column names to match your sheets

  • I seen something like that just a minute ago while searching. The problem with that method is there are several sheets involved that are close copies of each other. Adding a column, will not out of the realm of possibility, would be a pain when moving items from sheet to sheet.

  • I managed to get everyone's approval to add the column. Second additional question, can I make the criteria in the FIND function dynamic? It would make it easier if I could make the criteria equal a cells value, so I could have my primary column be the word I am looking for.

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Are the possible answers in a list in your target sheet- for example "Accurate" is listed in a row? That makes it easy with a [column name]@row reference. Can you mock up how your data is arranged?

  • Travis Myers
    Travis Myers ✭✭
    edited 01/11/21

    Right now it is a pretty fresh sheet. I have a couple columns labeled with the months, then my primary column is the list of business's names that I could reference.

    My column name is Contractor the down that column it simply goes:

    contractor1

    contractor2

    contractor3

    etc.

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    If your target sheet has the word you're looking for, you can use the syntax

    COUNTIFS({Range of source sheet}, FIND([target sheet column name]@row, @cell) > 0)

  • That works wonderfully. You saved me tons of typing. Thank you!

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Glad to help. I also just noticed in your original formula you might also save yourself some editing by using the Month function MONTH@cell = 1, which encompasses your formula <x> date range. If the data doesn't have to be recorded as a snapshot, you could use MONTH(TODAY()) which will always be looking at the current month. If I do have to keep records of the 'snapshots' I use an 'archive' sheet and leverage Copy Rows in the automation.

    cheers

    Kelly

  • I have been tinkering with adding in the month function, but haven't quite figured out how to correctly modify the syntax.


    I tried removing my AND statement and adding in the MONTH function with MONTH(@cell) = 1) for January but have not gotten it to work just yet.

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Hey

    Month(@cell) is correct - you're showing a paranthesis afterwards - unless that is the final closing one for the formula that shouldn't be there. I am assuming that your Range 2 is formatted as a date column.

    {FOLLOW UP Range 2}, IFERROR(MONTH(@cell), 0) = 1

    I added the IFERROR since date fields are notorious for throwing errors if they are blank- not just with the MONTH function but anytime.

    See if that works for you

    Kelly

  • I should have waited a little longer to ask. A little more tinkering at I figured it out. I had to keep my AND statement to get it to work.

    Final result:

    =COUNTIFS({FOLLOW UP Range 1}, AND(IFERROR(MONTH(@cell) = 1, 0)), {FOLLOW UP Range 3}, @cell = "green")


    This is a slightly different formula I am using in another sheet.

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    I'm glad you got it to work although I don't know why it was required. The terms in a Countifs formula are already ANDs. But hey, whatever works!

    Give a shout if there's anything else the community can help you with

    cheers,

    Kelly

  • I did come across one other minor question. How would I correctly add YEAR to that formula? I know the formula itself I am after is YEAR(@cell) <> 2020, but can't seem to correctly incorporate it.

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Here's an example of one

    =COUNTIFS({Test Sheet Start Date}, IFERROR(MONTH(@cell), 0) = 1, {Test Sheet Start Date}, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()))

    If you type out the year, it must be yyyy, for example = 2021

  • I've been trying to use your instructions to exclude children from a count but I am a little confused by the helping column. If it is a checkbox where do I add the first formula in your answer (=IF(Count(Children(Primary@row)>0, 1))?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!