Looking to count how many 'scheduled' statuses we have in 2023 by month.

PKane

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:

Paul Newcome

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

PKane

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

Paul Newcome

That last range should be the same as the other ranges.

{Range}

not

{Range}:{Range}

PKane

@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)

Paul Newcome

Yes. Is that working for you?

PKane

@Paul Newcome It's returning an #invalid Ref error. I double checked my references and can't see an issue with them.

Paul Newcome

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.

PKane

Hi @Paul Newcome I checked them all and they all say "Edit Reference"

Paul Newcome

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}?

PKane

@Paul Newcome

AC UPS Core OPCO reference:

AC UPS Year reference:

AC UPS Status reference:

Scheduled Install Date reference:

PKane

@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

Paul Newcome

Yes. That last 1 is the month number. You currently have it looking for January.

PKane

@Paul Newcome thanks for confirming that. The others are all set to the correct month - Sept/9, etc. and they aren't counting correctly.

Paul Newcome

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?