#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)
Best Answer
-
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])
Answers
-
VERY VERY close!
=SUMIF([Reconciled Date]:[Reconciled Date], YEAR(@cell) = YEAR(TODAY()), [Total Rebate]:[Total Rebate])
-
@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?
-
It should still work in the sheet summary. Is your Reconciled Date column formatted as DATE?
-
@Carson Penticuff Yes, Reconciled Date is a DATE column.
-
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))
-
@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:
-
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])
-
@Carson Penticuff That worked! Thank you!! So does that mean there is an error somewhere in the date column?
-
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], <> "")
-
Plugged in formula and it returns "0". Very strange.
-
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.
-
Thank you so much for your help!
Help Article Resources
Categories
Check out the Formula Handbook template!
