I am getting an invalid data type when the IF function pulls the false statement which is blank inst
=SUM(IF(AND(YEAR(INDEX({Draw 1 Date Received}, MATCH([TK ID#]@row, {FEE TKID}, 0))) = 2024, MONTH(INDEX({Draw 1 Date Received}, MATCH([TK ID#]@row, {FEE TKID}, 0))) = 11, DAY(INDEX({Draw 1 Date Received}, MATCH([TK ID#]@row, {FEE TKID}, 0))) < 15), INDEX({Draw 1 Received}, MATCH([TK ID#]@row, {FEE TKID}, 0)), ""), IF(AND(YEAR(INDEX({Draw 2 Date Received}, MATCH([TK ID#]@row, {FEE TKID}, 0))) = 2024, MONTH(INDEX({Draw 2 Date Received}, MATCH([TK ID#]@row, {FEE TKID}, 0))) = 11, DAY(INDEX({Draw 2 Date Received}, MATCH([TK ID#]@row, {FEE TKID}, 0))) < 15), INDEX({Draw 2 Received}, MATCH([TK ID#]@row, {FEE TKID}, 0)), ""), IF(AND(YEAR(INDEX({Draw 3 Date Received}, MATCH([TK ID#]@row, {FEE TKID}, 0))) = 2024, MONTH(INDEX({Draw 3 Date Received}, MATCH([TK ID#]@row, {FEE TKID}, 0))) = 11, DAY(INDEX({Draw 3 Date Received}, MATCH([TK ID#]@row, {FEE TKID}, 0))) < 15), INDEX({Draw 3 Received}, MATCH([TK ID#]@row, {FEE TKID}, 0)), ""))
Best Answer
-
My next guess would be that there are instances where your INDEX/MATCHes aren't pulling in dates. This could also throw that error when using date based functions such as YEAR, MONTH, and DAY. Try wrapping each of those in an IFERROR to output zero as well.
IFERROR(YEAR(index/match), 0)
Answers
-
Try outputting a zero instead of a blank.
-
already tried with just 0 and "0" and still got the same result of invalid data type unfortunately
-
My next guess would be that there are instances where your INDEX/MATCHes aren't pulling in dates. This could also throw that error when using date based functions such as YEAR, MONTH, and DAY. Try wrapping each of those in an IFERROR to output zero as well.
IFERROR(YEAR(index/match), 0)
-
You are a genius !!!
-
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.6K Get Help
- 435 Global Discussions
- 152 Industry Talk
- 495 Announcements
- 5.3K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 77 Community Job Board
- 508 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 308 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
Trending in Formulas and Functions
- I am working in a separate sheet for calculating metrics. I want to count the number of items in a referenced date range in another sheet that meet certain year criteria. For example, I want to find t…
- I have a column that is a contact list where a single or multiple staff members can be selected to be scheduled. I'm trying to create a formula that counts how many times each individual's names are l…
- I have two columns I am using for this formula: "Date Received" and "Days Duration (Received to Listed)". I want the formula below to look at any items in the Date Received column between 1/1/25 and 1…