Cross Sheet reference involving dates shows INVALID DATA TYPE

Vivien Chong

I am working on some inventory check and report.

Inventory sheet as follows:

Metric sheet to report on monthly incoming by items and month as follows:

I am trying to refer to the Inventory Sheet to sum Qty In if:

1) Level is 1 (I don't want the parent row to be calculated)

2) Equipment Description matches the primary column

3) Month matches [Nov20]2

4) Year matches [Nov20]1

But I keep getting invalid data type error.

Can anyone assist? Thanks.

Find more posts tagged with

Accepted answers

Paul Newcome

Try @cell references instead of ranges:

=COUNTIFS({Date Range}, MONTH(@cell) = 12)

I also notice there are blanks in the source data. This could also throw an error. To account for these, I like to use an IFERROR.

=COUNTIFS({Date Range}, IFERROR(MONTH(@cell), 0) = 12)

All comments

Paul Newcome

Try @cell references instead of ranges:

=COUNTIFS({Date Range}, MONTH(@cell) = 12)

I also notice there are blanks in the source data. This could also throw an error. To account for these, I like to use an IFERROR.

=COUNTIFS({Date Range}, IFERROR(MONTH(@cell), 0) = 12)

Vivien Chong

@Paul Newcome thanks.

I manage to work out on the formula base on your input.

Paul Newcome

Happy to help. 👍️

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