I am getting an invalid data type when the IF function pulls the false statement which is blank inst

reed.harper

=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)), ""))

Paul Newcome

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)

Paul Newcome

Try outputting a zero instead of a blank.

reed.harper

already tried with just 0 and "0" and still got the same result of invalid data type unfortunately

Paul Newcome

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)

reed.harper

You are a genius !!!

Paul Newcome

Happy to help. 👍️