Looking to count how many 'scheduled' statuses we have in 2023 by month.
Hello,
I'd like my formula to look at my AC UPS Scope sheet (source sheet) and count how many scheduled statuses we have by month in 2023.
This is what I have assembled thus far - it keeps coming back as #unparseable.
I probably have it far from correct at this point - any help in correcting/pointing in the right direction would be greatly appreciated.
=COUNTIFS({AC UPS Scope - LFCM Core OPCO}, "NSP", {LFCM OT AC UPS Year}, "2023", {LFCM OT AC UPS Status}, “Scheduled”, [INITIAL DATE]:[INITIAL DATE], IFERROR(MONTH(@cell), 0) = 1)
The source sheet:
Answers
-
You should have a {Cross Sheet Reference} for that last range.
Also looks like you may have an issue with your quotes. See how some are straight up and down ("NSP") and others are slanted (“Scheduled”)? The slanted ones are called "smart quotes" which (ironically enough) are not recognized as valid characters in a Smartsheet formula. You will need to retype them here in the Community, directly in your sheet, or in a text editor such as Notepad (not Word).
-
@Paul Newcome thanks for pointing out the issue with the quote types - had no idea about that.
As for the formula - this is what I have modified it to, but I am still getting #Unparseable:
=COUNTIFS({AC UPS Core OPCO}, "NSP", {AC UPS Year}, "2023", {AC UPS Status}, "Scheduled", {LFCM OT Scheduled Date}:{LFCM OT Scheduled Date}, IFERROR(MONTH(@cell), 0) = 1)
I appreciate your help with this, Paul.
-
That last range should be the same as the other ranges.
{Range}
not
{Range}:{Range}
-
@Paul Newcome like this?
=COUNTIFS({AC UPS Core OPCO}, "NSP", {AC UPS Year}, "2023", {AC UPS Status}, "Scheduled", {LFCM OT Scheduled Date}, IFERROR(MONTH(@cell), 0) = 1)
-
Yes. Is that working for you?
-
@Paul Newcome It's returning an #invalid Ref error. I double checked my references and can't see an issue with them.
-
That particular error means you have a {Range} in the formula that hasn't been set up. If you click inside of each {Range}, there should be a little blue link in the formula helper dropdown box that says "Edit Reference". If it says "Insert Reference" then you need to create the cross sheet reference following the proper process.
-
Hi @Paul Newcome I checked them all and they all say "Edit Reference"
-
Can you provide a screenshot of the formula open in the sheet as if you are about to edit it as well as screenshots of each "Edit Reference" in all of your {Ranges}?
-
AC UPS Core OPCO reference:
AC UPS Year reference:
AC UPS Status reference:
Scheduled Install Date reference:
-
@Paul Newcome I don't know what happened - but now after I came back from break its not erroring out, but unfortunately its not counting either.
I'm unsure how to specify in the formula which month its supposed to look for the scheduled dates in. Is it the last area?
This is the scope sheet (source) filtered to show 2023 scheduled status items
This is the target sheet with the formula showing 0's
-
Yes. That last 1 is the month number. You currently have it looking for January.
-
@Paul Newcome thanks for confirming that. The others are all set to the correct month - Sept/9, etc. and they aren't counting correctly.
-
How is your year column being populated, and is there a reason you are pulling that separately instead of directly from the same date as the month?
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!