INVALID DATA Type when using countifs formula referencing from another sheet
Hi,
I am computing for weekly counts of leads generated for my report and I am using countifs formula and refer to the working sheet where my data is located.
I am pretty sure that I am referencing in the correct Date Column and it's data type is also a Date. But the formula is prompting an "Invalid data type error". When I try to use the formula to the working sheet itself and select a range of cells, it is working. Please see the screenshot below.
The first image shows my formula referencing to the working sheet, but when I hit enter it prompts "INVALID DATA TYPE". The second image is the my reference column. The third image is when I tried the formula to the working sheet itself and it is working.
Please help on how can I fixed the "INVALID DATA TYPE" error.
Answers
-
Hi @Benjo09
Try this.
=COUNTIFS({Fresh Leads Master Range 1}, IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER(TODAY()))
Did that work?
I hope that helps!
Be safe and have a fantastic week!
Best,
Andrée Starå
Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Hi @Benjo09
The error occurs when it's looking at the entire column, which includes blank cells that aren't dates. (You'll notice that for the formula that's in the sheet itself, you have a specific range listed - from rows 1-12, which is why it doesn't error).
To fix this, we can just wrap an IFERROR statement around your WEEKNUMBER(@cell) function to say that if there's an error there (so, if the cell is not a date) just read it as "0" instead.
Try this:
=COUNTIFS({Fresh Leads Master Range 1}, IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER(TODAY()))
Let me know if this works for you!
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!