How to use countif / countifs where parent rows are excluded in a reference sheet.

Hello

I am making a dashboard where it details the number of completed, in progress, not started and overdue projects. I use the widget "metric".

I get the data from a secondary sheet (an extra support sheet to the main one) which gives an overview. In this sheet I use "Countif" to count the amount of projects in each state of completed, in progress, not started and overdue

The problem is that the "countif" includes the parent row. This makes it seem as though the parent row are separate tasks when they aren't. This results in the total amount of projects to be off by a surplus of 24.

I tried using Countifs where it is supposed to only count the rows not marked in a checkbox column:

=COUNTIFS({Planning area 80, 60 & 10 rev1 Range 1}, ="Completed";{Planning area 80, 60 & 10 rev1 Range 2}:{Planning area 80, 60 & 10 rev1 Range 2}, ="0")

Is the Countifs wrong, or is there another way of accomplishing my goal?

Thank you in advance.

Find more posts tagged with

Accepted answers

All comments

Bassam Khalil

Hi @Frederik Soerensen

Hope you are fine, Please add a copy of your sheet or a screenshot (after removing or replacing any sensitive information). This will make it easier for me to provide the appropriate answer to your question.

Paul Newcome

Your syntax is a little off. Notice how you have the first range listed only once, but then you listed the second range twice separated by a colon? Try removing the colon and second occurrence of the second range so that it is similar to the first. Additionally, if you are evaluating a checkbox column, you are going to want to remove the quotes from around the zero.

=COUNTIFS({Planning area 80, 60 & 10 rev1 Range 1}, ="Completed";{Planning area 80, 60 & 10 rev1 Range 2}:{Planning area 80, 60 & 10 rev1 Range 2}, =0)

=COUNTIFS({Planning area 80, 60 & 10 rev1 Range 1}, ="Completed";{Planning area 80, 60 & 10 rev1 Range 2}, =0)

Frederik Soerensen

Hello Bassam, sorry if I was unclear in my explanation.

Underneath is my grid-sheet.

As you can see, there are around 50-60 tasks. Some of these are parent rows of course.

Here is a work in progress layout for a Dashboard:

The small boxes that says 9, 8, 175 and 0 are found using the widget called Metric (the current numbers are irrelevant). The intent is for them to count how many tasks are either completed, in progress, not started or overdue. The parent rows are not supposed to count as a task in these boxes.

To get these widgets I reference to this other grid-sheet:

here I use countif (reference to the first grid-sheet) to count the amount of tasks in each status. This however includes parent rows.

How do I exclude the parent rows in the countif?

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.