COUNTIF WITH CONTAINS/ AND/OR

Mary Kam

=COUNTIFS({TABLE TYPES OF APPLE Range 2}, CONTAINS("apples", @cell), {TABLE TYPES OF APPLE Range 1}, OR(CONTAINS(@cell, "red"), CONTAINS(@cell, "green"), CONTAINS(@cell, "yellow")))

My formula does not seem to work. I would like to countif if it is apples in range 2 and range 1 contains red, or green, or yellow. In addition I would like to add range 2 does not contain exp, bruise, damaged.

Thank you in advance.

Mary Kam

Thank you again for your prompt reply. the formula is resolved.

=COUNTIFS({TABLE TYPES OF APPLES Range 2}, CONTAINS("RED", @cell), {TABLE TYPES OF APPLES Range 1}, OR(CONTAINS("GREEN", @cell), CONTAINS("YELLOW", @cell), CONTAINS("PINK", @cell)), {TABLE FORCE OVERTIME ACTIVITY Range 1}, NOT(OR(CONTAINS("EXP", @cell), CONTAINS("BRUISE", @cell))))

Bassam Khalil

Hi @Mary Kam

Hope you are fine, Please add a copy of your sheet or a screenshot (after removing or replacing any sensitive information). This will make it easier for me to provide the appropriate answer to your question.

Or if you like to fix the formula directly on your sheet please share me as an admin on a copy of your sheets ( Source & Destination ) and i will write the exact formula for you then you can copy it to your original sheet.

Mary Kam

Thank you again for your prompt reply. the formula is resolved.

=COUNTIFS({TABLE TYPES OF APPLES Range 2}, CONTAINS("RED", @cell), {TABLE TYPES OF APPLES Range 1}, OR(CONTAINS("GREEN", @cell), CONTAINS("YELLOW", @cell), CONTAINS("PINK", @cell)), {TABLE FORCE OVERTIME ACTIVITY Range 1}, NOT(OR(CONTAINS("EXP", @cell), CONTAINS("BRUISE", @cell))))

Marlayna Sullivan

Hello,

I am working to set up a COUNTIF formula where I have a field that contains multiple entries. I am using the CONTAINS function so that the count will return all rows that have one potential entry in it, and that it meets a quarter, year, and status that are set. The error message I am getting in Incorrect Argument Set, but I am at a loss. Any insight on what I am missing would be greatly appreciated. The formula I have pulled together is below.

=COUNTIFS(CONTAINS("MAXe", System:System), [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")

Thank you.

Genevieve P.

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

Genevieve P.

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

Nick Stamatakis

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!

Genevieve P.

Hey @Nick Stamatakis

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

Nick Stamatakis

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!

Genevieve P.

Hey @Nick Stamatakis

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?

Nick Stamatakis

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!

Screenshot 2023-10-13 at 11.50.33 AM.png

Genevieve P.

Hey @Nick Stamatakis

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?

Nick Stamatakis

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?

Genevieve P.

Hey @Nick Stamatakis

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)?

Nick Stamatakis

Hi @Genevieve P.

I can confirm that there is only 1 sheet feeding that Report. Here are some screenshots to help troubleshoot:

Screenshot #1: This is a summary sheet where all projects are managed and contains all the referred to columns in the formula. You can see that the formula we've exchanged is a summary sheet cell that results in 7.

Screenshot #2: Same as above but displaying the Start and End Dates that the formula refers to.

Screenshot #3: This is the complementary report with the same filters and results in a value of 10.

Screenshot #4: Confirmation that it is only 1 sheet feeding this report.

Screenshot #5: Showing the filters applied in the Report.

Screenshot #6: This is the result of the formula we validated in the Portfolio Summary sheet (Screenshots 1 and 2) without the formula displaying.

I hope this helps; happy to provide more info to resolve this issue. Many thank you's in advance!