Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
Help with formula: COUNTIFS
I am trying to count the tasks not started yet so the column [% complete] is either blank or 0. I used this formula-Β COUNTIFS([% Complete]:[% Complete], "",Β [% Complete]:[% Complete], 0) but getting 0 as result whereas more than half of my tasks have not started yet. Any feedback highly appreciated. Thank you.Β
Comments
-
Also, related to this, when I use this formula- "=COUNT([% Complete]:[% Complete])", Β it gives me 55 whereas there are 62 rows. Does it not count the parent rows?Β
-
Ginny,
Β
COUNTIFS must match all criteria, not just one of them, so you are looking for both blank and 0, not either.
Β
COUNT is not counting the number of rows for a Text/Number column, but non-blanks.
It will count the rows for a Checkbox type column (both checked and unchecked), but be careful, it will usually count the extra 9 empty rows at the bottom of the sheet too.
Verify that you have 7 blank entries in the [% Complete] column to ensure I am correct.
Β
This formula gets us close - but still counts those extra blank rows at the bottom of my sheet:
Β
=COUNTIF([% Complete]:[% Complete], OR(ISBLANK(@cell), @cell = 0))
Β
To resolve this, I assumed that only rows with a defined task would be counted and changed the formula back to the COUNTIFS
Β
=COUNTIFS([% Complete]:[% Complete], OR(ISBLANK(@cell), @cell = 0), Task:Task, NOT(ISBLANK(@cell)))
Β
Hope that helps.
Β
Craig
Β
Β
Β
Β
-
Hi Craig,
Β
This is very helpful! So, I tried the formula below to count the %complete "in progress" defining it as the ones that is not complete (not 100%) or not started (not 0 or blank). It seems to have worked but was wondering if there is a better way to construct the formula. I would love to hear your thoughts on it. Thank you.
Β
=COUNTIFS([% Complete]:[% Complete], AND(NOT(ISBLANK(@cell)), @cell <> 1, @cell <> 0), [Task Name]:[Task Name], NOT(ISBLANK(@cell)))
-
Ginny,
Β
That looks OK to me.
You might be able to change <> to < and > , but that is likely cosmetic.
<> for both will result in hits on negative values or greater than 100% values.
Β
Because you are using COUNTIFS, you could also get rid of the AND(), but your version is more compact.
Β
You MIGHT have a misplaced end paranthesis
Β
=COUNTIF( Β complete%range, Β AND(criterion for complete%), task-namerange, criteria for task-name)
Β
Craig
Β
-
Β I tried using <and> and kept getting #UNPARSEABLE.Β
Β
I changed it to the formula below seems to be giving the right result. I took out validating Task name as I am not counting blank cells at all. Let me know if I can improvise it further. Thanks Craig!
Β
=COUNTIF([% Complete]:[% Complete], AND(NOT(ISBLANK(@cell)), @cell <> 1, @cell <> 0))
Β
-
Look great!
Β
Craig
-
Just curious, can you give me an example how to use < and >? I could not find any. Thanks Craig!Β
-
Ginny,
Β
I'm not sure what the question is.
Β
=COUNTIF([% Complete]:[% Complete], > 0.5)
or
=COUNTIF([% Complete]:[% Complete], @cell> 0.5)
Β
will count percentages greater than 50%
Β
Craig
Β
-
Hi All & Hopefully Craig - I had a formula work perfectly well last week, but this morning I was getting a #NO MATCH.Β Data has not changed.Β I do have this looking up data which is pulling from an INDEX, MATCH formula, but when I changed a piece of that source data to a value it still did not work.Β
Any thoughts on this?
Here is the formula - Counting dates in the column for the month of August:
=COUNTIFS([File Upload Date]:[File Upload Date], @cell >= DATE(2018, 8, 1), [File Upload Date]:[File Upload Date], @cell <= DATE(2018, 8, 31))
