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

Christian Wells

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

Find more posts tagged with

Formulas

Comments

Tomasz Giba

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

=IF(ISBLANK(B1,0,1)

=IF(TODAY()<C1,0,1)

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.

APJ Enablement

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

https://app.smartsheet.com/b/publish?EQBCT=c2b0559072f848daa87c55163c279017

New Column: Expired or Blank

Parent Row:

=SUM(CHILDREN())

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

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,