#INVALID DATA TYPE, when trying to Column count Quantity of Rows with a Date by Month.

Options
✭✭✭

I believe I am close, but no cigar yet...

I have a request sheet that records a completed date in a Date Column (ie. '01/07/22').

I am trying to count the # of requests that are completed by Month type for a report. Ideally would like to report how many requests completed in January, February, March, etc...

I realize the #INVALID DATA TYPE occurs because not all cells within the date column have a request posted, hence no date, hence no DATA.

=COUNTIFS([Column6]1:[Column6]8, MONTH(@cell) = 1

This equation works (counting January requests completed) because the 'range' is limited to cells that have dates recorded. Once I expand out of this 'range' to include non-date recorded cells (entire column) I receive the error message. I have tried to include an 'IFERROR' into the equation, but cannot quite seem to get it to work.

Much appreciated in advance for the help. Thank you and Happy New Year!

-Robert

• ✭✭✭✭✭✭
Options

Hey @Robert B

Try this

=COUNTIFS([Column6]1:[Column6]8, IFERROR(MONTH(@cell),0) = 1)

Another version:

=COUNTIFS([Column6]1:[Column6]8, ISDATE(@cell), [Column6]1:[Column6]8, MONTH(@cell) = 1)

Kelly

• ✭✭✭✭✭✭
Options

Hey @Robert B

Try this

=COUNTIFS([Column6]1:[Column6]8, IFERROR(MONTH(@cell),0) = 1)

Another version:

=COUNTIFS([Column6]1:[Column6]8, ISDATE(@cell), [Column6]1:[Column6]8, MONTH(@cell) = 1)

Kelly

• ✭✭✭
Options

Brilliant!

Works splendidly!

Thank you @Kelly Moore

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!