COUNTIFS + AND Formula does not Equal the Filter Count
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 NonBoard Items
Status  has none of  Awaiting CRF, Docketing, Fully Executed, Not Renewed, On Hold, Submitted to Purchasing, Withdrawn, TESTING.
Answers

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

Hi @Courtney S. ,
Sorry, it is a lot, but here are the filters.

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 letterperfect. But I'm not seeing anything obvious like that!
If the filter is pulling in everything you want & expect, then maybe try completely rebuilding 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? 
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.

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! 
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. 
If the Status column is a multiselect column you might want to look into NOT + HAS for selecting the values to exclude, in your formula.

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 NonBoard Items"), 1, 0)
What this does: If the value in theContracts In Process 2024 BC/PO
column matches any of the specified terms, the formula will return1
. Otherwise, it will return0
.
 _______________________________________________________
 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 theContracts In Process 2024 Status
column does not match any of the excluded terms, the formula will return1
. Otherwise, it will return0
.
 _______________________________________________________
 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.
Marcé Holzhauzen
Solution and Delivery Consultant with Award Winning Partner
Want to meet?Check my availabilitywww.prodactive.co.uk
If this helped, help me & the SSC by accepting it and reacting w/💡insightful, ⬆️ Vote Up, and/or ❤️Awesome.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.8K Get Help
 406 Global Discussions
 218 Industry Talk
 457 Announcements
 4.7K Ideas & Feature Requests
 141 Brandfolder
 136 Just for fun
 57 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 297 Events
 37 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!