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.
Answers
-
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.
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
-
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)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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?
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 438 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 451 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 282 Events
- 32 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!