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

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

Tags:

Comments

  • Ginny A.
    Options

    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 ✭✭✭✭✭✭
    Options

    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

     

     

     

     

  • Ginny A.
    Options

    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
    Options

    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

     

  • Ginny A.
    Options

     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 ✭✭✭✭✭✭
    Options

    Look great!

     

    Craig

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

    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 ✭✭✭✭✭✭
    Options

    Ginny,

     

    I'm not sure what the question is.

     

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

    or

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

     

    will count percentages greater than 50%

     

    Craig

     

  • Chris H.
    Options

    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.