COUNTIF that will only count child rows when present

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).

Find more posts tagged with

Formulas

Accepted answers

Kelly Moore

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

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)

All comments

Kelly Moore

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

Travis Myers

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.

Travis Myers

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

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

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

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)

Travis Myers

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

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

Travis Myers

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

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

Travis Myers

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

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

Travis Myers

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

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

Andrew Lucia

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))?

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.