Countif formula... using crosssheet formula

Debbie J

I've tried both... they don't work they say unparseable... what am i missing

I want to

I am looking to add up all P1's that have a region of America Break Fix and a status that is not finished or cancelled, then count all the P1's in the priority column.

 

 

=COUNTIF({CS Current Months WO status - Status}="Open",{CS Current Months WO status - Status}="Scheduled",{CS Current Months WO status - Status}="On Site",{CS Current Months WO status - Status}="Parts On Order",{CS Current Months WO status - Status}="Pending Customer Action",{CS current Months WO status Region} = "Americas- Break-Fix"), {CS current Months WO status Priority} = Category8)

 

=IF(OR({CS Current Months WO status - Status}<>"Finished",{CS Current Months WO status - Status}<>"cancelled"),COUNTIF({CS current Months WO status Region} = "Americas- Break-Fix"), {CS current Months WO status Priority} = Category8),""))

 

 

J. Craig Williams

It will be difficult to properly debug this as there is a lot going on that has to be assumed. 

And to be blunt, I'm not really sure where to begin. 

However...

Taking a look at your first formula:

=COUNTIF({CS Current Months WO status - Status}="Open",

{CS Current Months WO status - Status}="Scheduled",

{CS Current Months WO status - Status}="On Site",

{CS Current Months WO status - Status}="Parts On Order",

{CS Current Months WO status - Status}="Pending Customer Action",

{CS current Months WO status Region} = "Americas- Break-Fix"),

 {CS current Months WO status Priority} = Category8)

1. COUNTIF() takes TWO arguments, a range and a criteria.

You have EIGHT arguments. If the rest was valid, you should use COUNTIFS(), which takes arguments in pairs. (I always use COUNTIFS() as there is no benefit to using COUNTIF() except one typed letter)

Read up on COUNTIFS() here.

https://help.smartsheet.com/function/countifs

2. COUNTIFS() however, you will find won't work either. (Assuming you try that by replacing all the equal signs (=) with commas (,) - resulting in 16 arguments). What COUNTIFS is doing looking for all items in your range match ALL of the criteria. But ... Status will never be both 'Open' and 'Scheduled', so you'll get 0.

Likewise, your second attempt

=IF(OR({CS Current Months WO status - Status}<>"Finished",

{CS Current Months WO status - Status}<>"cancelled"),

COUNTIF({CS Current Months WO status Region} = "Americas- Break-Fix"),

 {CS current Months WO status Priority} = Category8),

""))

also has a misunderstanding of the logic of these formulas. 

3. Taking just the first two arguments of the OR() function

OR({CS Current Months WO status - Status}<>"Finished",

{CS Current Months WO status - Status}<>"cancelled"),

If [Status] is 'Finished', then it not 'Cancelled' and if it is 'Cancelled' then it is not 'Finished', so the OR() returns TRUE, regardless of the rest of the arguments.

I suggest reading up on formulas and functions -- it will save you a lot of frustration. It did for me.

https://help.smartsheet.com/topics/formulas-and-functions

There's more, but let's cut to something that might work:

=COUNTIFS(Status:Status, NOT(OR(@cell = "Finished", @cell = "Cancelled")), Region:Region, "Americas- Break-Fix", Priority:Priority, "P1")

I've replaced your X-Sheet References with columns in the same sheet. I also don't know if you intended the priority to be Category8 or not, so I am using "P1".

This uses COUNTIFS and will count ALL the rows that match ALL of these:

- have Status not 'Finished' or 'Cancelled'  (including blanks)

- have Region listed as 'Americas- Break-Fix'

- have Priority equal to 'P1'

Good luck

Craig