Can't seem to get this simple formula right.
{Weighted Forecast} is USD amount column
{ExpectedClose} is a date column
This formula in row 8 shows "Invalid Data Type"
For row 9, I am replacing {ExpectedClose} with {CreatedDate} and it works well.
May I know what could be the error?
{CreatedDate} in the source sheet is an Auto Date column
{ExpectedClose} is manually input, however there are some blank rows. Could it be caused by the blank rows? How can I rectify this formula?
Thanks.
Best Answer

Hi @Vivien Chong, I simulated your scenario  yes the problem is that there are some blank cells in there so MONTH(<blank>) will result in error. One idea how to make this work is to amend your formula as follows:
=SUMIFS({Weighted Forecast}, {CreatedDate}, IFERROR(MONTH(@cell), 13) = Month@row,...)
It worked in my simulation.
Hope this helps...
Vojtech
Answers

Hi @Vivien Chong, I simulated your scenario  yes the problem is that there are some blank cells in there so MONTH(<blank>) will result in error. One idea how to make this work is to amend your formula as follows:
=SUMIFS({Weighted Forecast}, {CreatedDate}, IFERROR(MONTH(@cell), 13) = Month@row,...)
It worked in my simulation.
Hope this helps...
Vojtech

Thanks @Vojtech Gajdos. Your formula works.
But I do not understand why are we putting a "13" in the IFERROR formula?

Hi @Vivien Chong, the reason for IFERROR(MONTH(@cell), 13) is: since MONTH(@cell) returns error (MONTH function requires a date and if blank cannot cope) we need to use IFERROR() and replace the error message by some number but since we don't want to include the row into the sum, it needs to be a number which will not match the month and that is 13 (or any number greater than 13)  since such month obviously doesn't exist.
Hope it makes sense Vivien...
If you are satisfied with the answer, kindly please mark your post as Answered
Regards
Vojtech
Help Article Resources
Categories
Check out the Formula Handbook template!