Can't seem to get this simple formula right.

Vivien Chong

{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.

Find more posts tagged with

Accepted answers

Vojtech Gajdos

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

All comments

Vojtech Gajdos

=SUMIFS({Weighted Forecast}, {CreatedDate}, IFERROR(MONTH(@cell), 13) = Month@row,...)

It worked in my simulation.

Hope this helps...

Vojtech

Vivien Chong

Thanks @Vojtech Gajdos. Your formula works.

But I do not understand why are we putting a "13" in the IFERROR formula?

Vojtech Gajdos

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

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.