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

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,

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

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

 

=COUNTIFS(B:B,0,A:A,"")

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

Christian,

 

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.

 

Craig

Christian Wells

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

Dont like it, but I appreciate it.

Christian Wells

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.