Countif with Dates, Smartsheets keep adding blank lines, Invalid Data Results

David Edwards

Hi Guys, 

Firstly, thanks for the help.  I am trying to add records and have cumulitive counts based on dates.  I am counting the date column of the new input lines with the following formula:

=COUNTIF([Created Date]:[Created Date], TODAY() - @cell < 7)

for Records added in the last 7 days and:

=COUNTIFS([Created Date]:[Created Date], MONTH(@cell) = 2, [Created Date]:[Created Date], YEAR(@cell) = 2015)

 for those added in month 2 and year 2015.  

The problem is that i am getting an "Invalid Data Type" error every time a line is added, often by smartsheets automatically, that does not have the date filled in that column.  This means that I am constantly going back and deleting rows just so the formulas work.  I could add a column, have it check the date column for a date with a If formula, add a 1 for positive values, then use a Sumifs function to count that column when the formula checks for the appropriate date value, but this seems extremely circuitous, and i would love to figure out why this doesn't work and or to know how to stop smartsheets from adding lines at random times by itself. 

Any help would be appreciated.

Thanks,

dave

CanadaJim

Hi Dave,

You can always use the IFERROR function.  Then if there's an error, replace the result with "Invalid/Incomplete Data" or just blank "".

Would that work?

Jim

David Edwards

No, I don't think it would because the calculations are to add values in the string, so if the IFERROR goes produces a "", then there is no count so one empty row negates all counts in all other rows.

I tried the other option of adding a row, setting the values to 1 if a date was in the row and a "" if not, then summing the row for all values less then 7 days old.  The problem is that the below results in an "Invalid Operation" error.

=SUMIF([Created Date]:[Created Date], TODAY() - @cell < 7, Count:Count)

The error is negated by the following, which does not include the whole column and so is not responsive to subsequently added rows.

=SUMIF([Created Date]1:[Created Date]10, TODAY() - @cell < 7, Count1:Count10)

Jason Anderson

Similar issue I had: I was dealing with blank lines that smartsheet adds messing up a count I was trying to do. I ended up having the sheet count entries in another column that would give me a correct count of 'real' rows and then used that to frame up my dates count if that makes any sense. 

CanadaJim

Good point Jason!

If you would like to keep it in a single row, however, and there is nothing in the error rows, you could have IFFERROR present a zero.  That way it doesn't affect your totals but will still function without manual intervention.

=IFERROR(COUNTIFS([Created Date]:[Created Date], MONTH(@cell) = 2, [Created Date]:[Created Date], YEAR(@cell) = 2015),0)

Jim

David Edwards

Thanks for the help Jim and Jason.  I didn't follow Jason's recommendation though and I tried Jim's solution without luck. 

All of the IFERROR calculations don't seem to work for me as shown above in Jim's post because the IFERROR gives me a 0 for the error it all of the counts.  So if the count should be two, it gives me an error which gets turned into whatever the IFERROR part of the equation defaults to when it encounters an error. so anytime there is a blank anywhere in that column, none of the counts give me anything but an IFERROR default value.  Maybe i am not doing something right.

OK, so here is how I "Fixed" it, for now.  Certainly NOT acceptable, but at least it gives me the data.  I kept the calculation the way i have it in my first responses, with the entire columns included.  I then made a form to put all the data in, then deleted the last rows so that the calculations work, then i closed the sheet and don't open it again.  I can put additional data into it via the forms, but if I open it and scroll down to the bottom of the page, it adds the additional lines and screws the formulas up again.  All the data that i need from it is then linked to from other sheets and manipulated or presented there.  Like i said, not the best.  Anybody else have any recommendations?