Troubleshooting Sheet Summary "INVALID DATA TYPE"

Hoping that the community can explain this issue I cannot solve (nor can AI).

Today we added a Y2026 Savings column to our sheet. Column formulas were all updated as well to look for values with "effective dates" in their corresponding years.

Sheet Summary (for Dashboard) formulas broke returning "INVALID DATA TYPE".

Tried a test with simply =sum([Y2025 Savings]:[Y2025 Savings]), returned same error.

Screenshots below:

I'm completely stumped. Is it not possible to have two years on one sheet? I proposed creating a new sheet for 2026 and using cross-sheet referencing for reporting, but the preference is to keep all data within the one sheet.

Find more posts tagged with

Sheets

Formulas

Reports

Dashboards

Sheet Summary

Accepted answers

heyjay

Can you check your column Y2025 Savings and Savings Status if there are any cells with #errors? Its usually the culprit. Also double check if the columns Y2025 Savings is formatted as Text/Number.

All comments

heyjay

MeganF

I removed the new column and returned the Y2025 Savings formula back to the original and everything is back to normal.

The new question is how can I add a new column to start reporting 2026 in the same sheet?
Formula generated via Smartsheet AI tools Generate formulas: (replace 2026 with 2025 for the Y2025 Savings column)

=IF(OR(ISBLANK([Savings Effective Date]@row ), ISBLANK([Monthly Savings]@row )), 0, IF(YEAR([Savings Effective Date]@row ) = 2026, ((12 - MONTH([Savings Effective Date]@row )) * VALUE([Monthly Savings]@row )) + ((30 - DAY([Savings Effective Date]@row )) / 30 * VALUE([Monthly Savings]@row )), 0))

MeganF

Hi All -

The issue was solved. Found one tiny character off in the Y2025 column's formula that I kept missing until I did total side-by-side breakdown.

Thanks for the help!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.