Cross Sheet reference involving dates shows INVALID DATA TYPE

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.
Vivien Chong
Modern Work Management Consultant @ 57Network Consultancy Sdn Bhd
Connect with us: 57network.com
Best Answer
-
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)
Answers
-
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)
-
@Paul Newcome thanks.
I manage to work out on the formula base on your input.
Vivien Chong
Modern Work Management Consultant @ 57Network Consultancy Sdn Bhd
Connect with us: 57network.com
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 67.9K Get Help
- 474 Global Discussions
- 208 Use Cases
- 517 Announcements
- 5.6K Ideas & Feature Requests
- 87 Brandfolder
- 157 Just for fun
- 84 Community Job Board
- 521 Show & Tell
- 36 Member Spotlight
- 3 SmartStories
- 309 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!