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

Ginny A.
Ginny A.
edited 12/09/19 in Archived 2017 Posts

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. 



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

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭



    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.







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

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭
    edited 03/13/17



    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)




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


  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    Look great!



  • Ginny A.
    Ginny A.
    edited 03/21/17

    Just curious, can you give me an example how to use < and >? I could not find any. Thanks Craig! 

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭



    I'm not sure what the question is.


    =COUNTIF([% Complete]:[% Complete], > 0.5)


    =COUNTIF([% Complete]:[% Complete], @cell&gt; 0.5)


    will count percentages greater than 50%




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

This discussion has been closed.