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.

Sheet Summary Row, with CountIFS to determine % and Dynamic references

Christian Wells
edited 12/09/19 in Archived 2016 Posts



Hi all,


Seeking Smartsheet Experts....!


I have a Company Register with columns to identify documents and expiry dates.

e.g. [A] Company Name, [B] License (Checkbox), [C] License Expiry (Date)

1) I want to calculate the summary of a count of All companies that do not have a license?

2) I want to calculate the summary of a count all Licenses Expired or blank?

That is identify the work left to do, in a list that will continue to grow..


I am trying       =COUNTIFS (B:B,0) + " to Confirm"           (The text is required to make the formula work as the checkbox field wants a 0 or 1 answer) ..This gives me 10 to confirm which is the total rows on the sheet (last row of data with 10 additional rows.... I could just -10 but that may become incorrect, so I dont trust it.  I've tried           =CountIFS(B:B,0,A:A,<>"")         but that always errors with unexpected operator for the <> symbol...
Is there a way to have COUNTIF or COUNTIFS count Blank Cells?

Ansd Also in line with the above issue I want to calculate those dates which are < today (expired), but the same error with unexpectd operator....

Is there a way to make smartsheet read the operator corretcly? encapsulate in a % symbol or something..I have tried a few.......

From the above  I would like to put the 2 together;  that is...

Count IF not blank PLUS  Count if Expired to give me a number of documentes to collect.


Lastly, If the above is not possible I have thought I need to reference the last row and then subtract the Summary Row from the list to get a total number...As the list will continue to grow over time how do I use a CountIF or CountIFS to reference the last Row in the Range part of the formula?


Finally, I dont want to add further fields / columns to my Sheet I just want a simple line / Header to calculate the Summary information.


Thank you for any assistance....



  • Tomasz Giba
    Tomasz Giba ✭✭✭✭✭
    edited 01/27/16

    You can do it by adding two new columns [C] & [D], and in these columns you will have a formulas:




    Next step you know =CountIFS(C:C,1,D:D,0] 


    I'm not sure about 0 or 1 and  or > just mes with it, but the trick is these additional columns. These can be hidden and lock.


    Tomasz Giba

  • APJ Enablement

    I would add 1 column, hide it & put the summary in the parent row of your "Expire Date" column.




    New Column: Expired or Blank

    Parent Row:


    Child Rows

    =IF(OR($[Expire Date]3 < TODAY(), ISBLANK($[Expire Date]3)), 1, 0)


    Existing Column: Expire Date

    Parent Row:

    ="" + $[Expired or Blank]2

  • Christian Wells
    edited 01/27/16

    Thank You Tomasz and Kris,


    I am aware of the adding a column to get this answer alas, that was not the point of the question;    I am asking if there is a "NO COLUMN" Solution,

    as i would need to add in another 16 columns for the calculation of all the other columns I wish to summarise this same way.


    Any experts that can help beyond the Additional Column, I am eager to hear form you!


    Thank you to all in advance

  • Travis
    Travis Employee
    edited 01/27/16

    You can count blank cells with COUNTIFS, just remove the <>. Try this:



  • Christian Wells

    Thank you Travis,


    So Simple, How much did I overthink that!


    Now just to get a Count of Dates < today in the column????


    Thank you

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



    This (Count of Dates < today in the column) will likely need another column.

    The problems you will run into, I believe, are


    1. you can't use date comparison in COUNTIF

    2. you can't modify the values in the range criteria prior to the comparison


    which means you can't compare to (<today()) and you can't change the date field to allow a different comparison of text or number.


    Unless I have missed something.



  • Christian Wells

    Thank you, Craig, I appreciate the response...

    Dont like it, but I appreciate it.Smile

  • Christian Wells
    edited 02/10/16

    Hello all,


    I persisted untill I could no more.
    The second column (Hidden) was the only workable solution.


    Thank you Craig, Travis, Kris & Tomasz Giba for your valuable input.

This discussion has been closed.