COUNTIF WITH CONTAINS/ AND/OR

Hi @Marlayna Sullivan

A COUNTIFS requires the column/range to be listed first, then the criteria after it, like so:

System:System, "Criteria"

In your case the Criteria is the CONTAINS function. This means you need to first list the range, then say CONTAINS.

Try this:

=COUNTIFS(System:System, CONTAINS("MAXe", @cell), [What Year is This Change Needed?]:[What Year is This Change Needed?], "2021", [What Quarter is This Change Needed?]:[What Quarter is This Change Needed?], "Q2", [Change Status]:[Change Status], "Open")

Let me know if this works for you!

Cheers,

Genevieve

Marlayna Sullivan

Hi @Genevieve P.

You are BRILLIANT! Thank you so much! That formula was giving me a run for my money.

I hope you have a wonderful week and Thank You again!

Marlayna

@Marlayna Sullivan You're welcome! I'm glad I could help 🙂

Hi @Mary Kam @Genevieve P. !

I am experiencing the same sort of issue with filtering out the Product Categories I am trying to capture in my formula that went live with a Start Date between Oct 01 2023 and Oct 31 2023 OR an End Date between Oct 01 2023 and Oct 31 2023. My current formula is as follows:

=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(@cell = "Product 1", @cell "Product 2"))

I am having a hard time translating the nested OR conditions (either with a Start Date or End Date between Oct 01 2023 and Oct 31 2023) while looking at specific Project Categories. I am sure it's a simple shift of my formula but I am stuck.

I appreciate your help in advance!

I would do three separate COUNTIF statements: one for the rows that went live between a certain time, one for the rows that end in your timeframe, and one to subtract duplicates (where one row meets both criteria).

=(COUNTIFS(just the start date range) + Countifs(just the end date range)) - Countifs(both criteria)

Try this:

=(COUNTIFS([Start Date]:[Start Date], >=DATE(2023, 10, 1), [Start Date]:[Start Date], <=DATE(2023, 10, 31), [Project Category]:[Project Category], OR(@cell = "Product 1", @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(@cell = "Product 1", @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(@cell = "Product 1", @cell = "Product 2"))

Cheers,

Genevieve

Hey @Genevieve P. !

Thank you so much for the prompt reply. I took your formula and made some updates to it to see if it works but I still get the #UNPARSEABLE error. I understand the logic behind what you are doing but even when I had the third part of the formula as my only portion, I still got the same error. With the updates, this is what the formula looks like:

COUNTIFS(

[Start Date]:[Start Date], >=DATE(2023, 10, 1),

[Start Date]:[Start Date], <=DATE(2023, 10, 31),

[Project Category]:[Project Category], OR(@cell = “Product 1”, @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(@cell = “Product 1”, @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(@cell = “Product 1”, @cell = “Product 2”))

Laid out this way, do you see where I might need to correct to avoid this issue? Many thanks in advance!

Did you copy/paste in the formula from a note pad or somewhere else? I see the quotes have changed from being straight like this: " to curved “

Smartsheet will need the straight quotes " otherwise you'll get an error. Can you try re-typing the quotes directly into the cell?

Screenshot 2023-10-13 at 11.50.33 AM.png

Good catch @Genevieve P. ! This fixed my #UNPARSEABLE error.

I was thinking why I'd need to have the third section of the formula but I suppose this portion removes double counting entries that are already captured in one of the first two sections of the formula.

My next query is this: when I run this formula as a Sheet Summary cell, the result does not match what I am expecting when I compare it to the filters I have on a report trying to do the same action. That is, I create the report to see the line entries in question but I create a Sheet Summary cell so that it can display nicely on a Dashboard (both the Report and Sheet Summary field are pulling from the same sheet).

On my report, I can tell you that my first criteria is that the Products in question are the ones I selected AND the Start Date is Oct 01 2023 to Oct 31 2023 OR End Date is Oct 01 2023 to Oct 31 2023. The Start and End Dates are grouped as an OR condition; the Product and Dates are two criteria groups joined by an AND. I hope that makes sense; I've added a screenshot to assist in my explanation.

I am off by about 1-2 in each scenario and I've tried to remove the double counting portion of the formula (Third section) to see if it helps but alas, it does not.

Am I approached the formula correctly? Is it wrong for me to compare the results of the formula to the results of the Report? I am open to suggestions and not stuck on my formula structure.

Thanks in advance!

Yes you're correct - the third part of the formula is to remove duplicates (rows that have both the start and end dates within your parameters).

For the difference in Counts, the Report and Formula should be the same. My formula returns 8, and my report returns 8.

Which one is returning more? Is it possible that the values entered for your Project Category are different in the Report and the formula?

Hey @Genevieve P.

Thanks for the response! My formula is returning 7 whereas my Report (with filters) is reporting 10. I will say though, when I manually count the entries in my report, it is including some entries that have a start and end date both in October (which are eliminated in the formula I guess) but are legitimate entries to account for in my report. Looks like the same goes for your example. If I remove the double counting portion of the formula (just to test), it returns 9 (which doesn't solve the issue).

Any thoughts of where I am going wrong?

Do you have more than 1 sheet in your Report?

Can you show some screen captures of your full set-up, both the sheet and the Report (but block out sensitive data)?