COUNTIFS + AND Formula does not Equal the Filter Count

Mrangel

Good afternoon.

I am having issues with the below formula equaling the filter within the sheet that we are pulling form.

Please see the below and point me in the right direction to help fix this.

Formula:

Filter:

BC/PO/BL | is one of | PO, BL, SSA, Lease, License, Other Non-Board Items
Status | has none of | Awaiting CRF, Docketing, Fully Executed, Not Renewed, On Hold, Submitted to Purchasing, Withdrawn, TESTING.

Courtney S.

Hi @Mrangel it looks like the picture of the filter didn't get posted, could you add that?

Mrangel

Hi @Courtney S. ,

Sorry, it is a lot, but here are the filters.

Courtney S.

Thanks! I was hoping maybe it was something as simple as having one character in the formula different from the options selected in the filter, since it has to be letter-perfect. But I'm not seeing anything obvious like that!
If the filter is pulling in everything you want & expect, then maybe try completely re-building the formula from scratch, just in case there is some very minor difference.
For troubleshooting purposes, you could try splitting your COUNTIFS into two separate COUNTIF formulas, then build two filters to be the equivalents, and see if that helps find where the discrepancy is?

Courtney S.

I did find a minor note in the COUNTIFS function page that might be relevant for you: "Blank cells aren't counted when using <> (not equal to)".

COUNTIFS Function | Smartsheet Learning Center

So this would mean that the COUNTIFS formula isn't counting any rows with a blank Status, and I think the filter would be counting rows with a blank Status.

Mrangel

I'll try and split the COUNTIFS into two separate COUNTIF formulas, and then build separate filters to be those equivalents.

I saw that note also for COUNTIFS in the function page, however, it is my formula that is giving me more counts than the filter.

I'll keep you posted on what I find.

Thank you!

Courtney S.

Does your Status column allow more than one value per cell? The "Has None Of" filter option indicates that.
The formula is only excluding rows where the Status column has exactly each individual status value you are excluding as its whole answer. So, a row with two of the unwanted status answers together would not be excluded fom the formula. Compared to the filter, which is excluding all rows where at least one of the listed status values is in the Status column.

Courtney S.

If the Status column is a multi-select column you might want to look into NOT + HAS for selecting the values to exclude, in your formula.

HAS Function | Smartsheet Learning Center

COUNTIF with HAS formula — Smartsheet Community

MarceHolzhauzen

Hi @Mrangel

Although there possibly could be a large formulae that could get this right, it becomes long and difficult to decipher when things go wrong.

I would suggest adding 2 helper columns in your original sheet and splitting the function

• Helper Column 1: BC/PO/BL Check

• =IF(OR([Contracts In Process 2024 BC/PO]@row = "PO", [Contracts In Process 2024 BC/PO]@row = "BL", [Contracts In Process 2024 BC/PO]@row = "SSA",[Contracts In Process 2024 BC/PO]@row = "Lease", [Contracts In Process 2024 BC/PO]@row = "License",[Contracts In Process 2024 BC/PO]@row = "Other Non-Board Items"), 1, 0)
  
  What this does: If the value in the Contracts In Process 2024 BC/PO column matches any of the specified terms, the formula will return 1. Otherwise, it will return 0.

• _______________________________________________________

• Helper Column 2: Status Check

• =IF(AND([Contracts In Process 2024 Status]@row <> "Awaiting CRF", [Contracts In Process 2024 Status]@row <> "Docketed",[Contracts In Process 2024 Status]@row <> "Fully Executed", [Contracts In Process 2024 Status]@row <> "Not Renewed", [Contracts In Process 2024 Status]@row <> "On Hold", [Contracts In Process 2024 Status]@row <> "Submitted to Purchasing", [Contracts In Process 2024 Status]@row <> "Withdrawn", [Contracts In Process 2024 Status]@row <> "TESTING"), 1, 0)
  
  What this does: If the value in the Contracts In Process 2024 Status column does not match any of the excluded terms, the formula will return 1. Otherwise, it will return 0.

• _______________________________________________________

• Now, in the sheet where you are looking to do the metric, you are simply looking for the columns that have a 1 in both of the helper columns
  
  =COUNT(COLLECT({Helper Column 1}, @cell = 1, {Helper Column 2}, @cell = 1))

• or

• =countifs({Helper Column 1},1, {Helper Column 2},1

• Hope this helps.