#INVALID DATA TYPE - using YEAR(@cell) = YEAR(TODAY()

Please help with formula to SUM values in Total Rebate column, IF Reconciled Date year is THIS year.

=SUMIF([Reconciled Date]:[Reconciled Date], =YEAR(@cell) = YEAR(TODAY()), [Total Rebate]:[Total Rebate])

Reconciled Date is a Date Column

Total Rebate is a Text/Number (set to currency format) column that contains a formula: =SUM([Corp Rebate]@row + [Client Rebate]@row)

Find more posts tagged with

Accepted answers

Carson Penticuff

This will essentially ignore any errors with date formatting. It doesn't sound like that is your issue based on your last result, but just to be sure.

=SUMIF([Reconciled Date]:[Reconciled Date], IFERROR(YEAR(@cell),1) = YEAR(TODAY()), [Total Rebate]:[Total Rebate])

All comments

Carson Penticuff

VERY VERY close!

=SUMIF([Reconciled Date]:[Reconciled Date], YEAR(@cell) = YEAR(TODAY()), [Total Rebate]:[Total Rebate])

Ami Veltrie

@Carson Penticuff Thank you for the speedy response Carson!

One too many "=", go figure. I copied and pasted your formula directly into my sheet but I'm still getting '#Invalid Data Type'.

Does it matter that I'm using the Sheet Summary for this formula?

Carson Penticuff

It should still work in the sheet summary. Is your Reconciled Date column formatted as DATE?

Ami Veltrie

@Carson Penticuff Yes, Reconciled Date is a DATE column.

Carson Penticuff

I have this working on a sample sheet. You may try this formula and compare the results. It will verify that all cells in the column are formatted as dates. The result should match the number of entries in the column. It could be possible that, even though the column is formatted as DATE, one or more individual entries are being treated as text.

=COUNTIF([Reconciled Date]:[Reconciled Date], ISDATE(@cell))

Ami Veltrie

@Carson Penticuff I plugged in that formula and it returned the exact number of cells that have a date.

Here's a screen shot of Reconciled Date column set up:

Carson Penticuff

This will essentially ignore any errors with date formatting. It doesn't sound like that is your issue based on your last result, but just to be sure.

=SUMIF([Reconciled Date]:[Reconciled Date], IFERROR(YEAR(@cell),1) = YEAR(TODAY()), [Total Rebate]:[Total Rebate])

Ami Veltrie

@Carson Penticuff That worked! Thank you!! So does that mean there is an error somewhere in the date column?

Carson Penticuff

Yes, at least one cell in that column must be an issue. If you plug this in, it will count how many cell are not formatted as dates. If will also ignore any blank cells since the first few unpopulated rows can sometimes show up in the count.

=COUNTIFS([Reconciled Date]:[Reconciled Date], NOT(ISDATE(@cell)), [Reconciled Date]:[Reconciled Date], <> "")

Ami Veltrie

Plugged in formula and it returns "0". Very strange.

Carson Penticuff

Very strange, indeed! At least the previous formula is working okay. If I think of anything else it could be, I will post back here.

Ami Veltrie

Thank you so much for your 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.