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

Tags:

Comments

  • Ginny A.
    Ginny A. ✭✭

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

    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.
    Ginny A. ✭✭

    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

    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.
    Ginny A. ✭✭

    Β 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!

    Β 

    Craig

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

    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.
    Chris H. ✭✭

    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.