Created Date, error when referenced in formula
{Date Submitted} is a cross-sheet reference to a column of Created (Date). {Total Findings} is a cross-sheet reference to a column of numbers generated via a formula. [Week Number]@row is a primary column with numbers in it. Formula 1 below works fine. However, formula 2 returns a #INVALID DATA TYPE. How is this possible? This seems very nonsensical to me.
Formula 1:
=SUMIF({Date Submitted}, WEEKNUMBER(@cell) = [Week Number]@row, {Total Findings})
Formula 2:
=COUNTIFS({Date Submitted}, WEEKNUMBER(@cell) = [Week Number]@row, {Total Findings}, @cell > 0)
Best Answer
-
Okay, so apparently having an empty value in the date column will cause the error.... Is it just me or shouldn't the function be built to ignore the empty rows or is there a use case I'm not considering?
This is required for COUNTIFS but not SUMIFS, which is confusing to me. This is the solution:
=COUNTIFS({Date Submitted}, IFERROR(WEEKNUMBER(@cell), 0) = [Week Number]@row, {Total Findings}, @cell > 0)
We can return zero for the weeknumber because whatever value is returning error is not a date anyways, and there is no such thing as week 0, so we do not have to worry about accidentally counting that row.
This forum post got me to realize what the issue was:
https://community.smartsheet.com/discussion/51441/countifs-with-date
Answers
-
Okay, so apparently having an empty value in the date column will cause the error.... Is it just me or shouldn't the function be built to ignore the empty rows or is there a use case I'm not considering?
This is required for COUNTIFS but not SUMIFS, which is confusing to me. This is the solution:
=COUNTIFS({Date Submitted}, IFERROR(WEEKNUMBER(@cell), 0) = [Week Number]@row, {Total Findings}, @cell > 0)
We can return zero for the weeknumber because whatever value is returning error is not a date anyways, and there is no such thing as week 0, so we do not have to worry about accidentally counting that row.
This forum post got me to realize what the issue was:
https://community.smartsheet.com/discussion/51441/countifs-with-date
