COUNTIF WITH CONTAINS/ AND/OR

Genevieve P.

Aha! I think we've found the culprit! 🙂 These screen captures were very helpful, thank you.

I can see that your Project Category column is multi-select. In your Report, the filter is looking to see if the cells in that column "have any of" the two selections, meaning that rows will appear when "SCD - SCD" is listed along with something else.

However the formula is looking for an exact match of "SCD - SCD" being in the cell, meaning it cannot have anything else selected with it. (Or "SCD - CRQ" without anything else selected).

This is an easy fix! If you want your formula to include rows where those values are selected along with something else, all we need to do is use the HAS function to see if the cell has that value (versus is that value).

OR(HAS(@cell, "Product 1"), HAS(@cell, "Product 2")) 

Try:

=(COUNTIFS([Start Date]:[Start Date], >=DATE(2023, 10, 1), [Start Date]:[Start Date], <=DATE(2023, 10, 31), [Project Category]:[Project Category], OR(HAS(@cell, "Product 1"), HAS(@cell, "Product 2"))) + COUNTIFS([End Date]:[End Date], >=DATE(2023, 10, 1), [End Date]:[End Date], <=DATE(2023, 10, 31), [Project Category]:[Project Category], OR(HAS(@cell, "Product 1"), HAS(@cell, "Product 2")))) - COUNTIFS([Start Date]:[Start Date], >=DATE(2023, 10, 1), [Start Date]:[Start Date], <=DATE(2023, 10, 31), [End Date]:[End Date], >=DATE(2023, 10, 1), [End Date]:[End Date], <=DATE(2023, 10, 31), [Project Category]:[Project Category], OR(HAS(@cell, "Product 1"), HAS(@cell, "Product 2")))

Cheers,

Genevieve