Sheet formula Error Message

ldavies95

I have a column titled "Current Month", this column is comprised of checkboxes, I have the following formula automatically checking the box if it is one month prior (for auditing purposes); for example if the Date is 10/08/2024, I need to check all boxes where the date starts with 09.

=IF(AND(MONTH([Date of Transportation]@row) = MONTH(TODAY()) - 1, YEAR([Date of Transportation]@row) = YEAR(TODAY())), 1, 0)

With that said I am building a dashboard and utilizing sheet summaries to fill it.

I am trying to Count all checkboxes that are checked in the "Current Month" Column.

I have tried =COUNTIF([Current Month]:[Current Month], 1) but it is not working and bringing "INVALID DATA TYPE"

by using these formulas I am hoping for everything to automatically update every month so I don't manually have to change it on the dashboard.

Carson Penticuff

Your COUNTIF formula seems to be fine, I see no issue there. It may be possible that you have one or more entries in the [Date of Transportation] column that are not formatted as dates. Do you have the "Restrict to Dates Only" slider for that column enabled? It may help to include an IFERROR statement on your checkbox column formula.

If this sheet overlaps from year to year, you will have issues with the formula. The following formula will handle overlapping years and also includes the IFERROR statement.

=IFERROR(IF(AND(MONTH([Date of Transportation]@row) = MONTH(TODAY() - DAY(TODAY())), YEAR([Date of Transportation]@row) = YEAR(TODAY() - DAY(TODAY()))), 1, 0), "")

ldavies95

It works now! Thank you for your help.

Carson Penticuff

Awesome, I'm glad it worked for you!