Using COUNTIFS with CONTAINS as a Criterion

Brian McElligott

I have a multi select list column in Sheet 1, containing campuses. In sheet two I am trying to get a count of how many times each campus is listed in the campus column in sheet 1. Here is my current formula:

=COUNTIFS({Event Campus}, CONTAINS("Cypress", {Event Campus}))

The results are always "0" for each campus.

Given that the campus column in sheet 1 is multi select, I can not use just a text string for the criterion in the COUNTIFS formula (this would only count the instances where only the text string is listed in the campus column in Sheet 2).

Thoughts?

Paul Newcome

Try this:

=COUNTIFS({Event Campus}, CONTAINS("Cypress", @cell))

Paul Newcome

Your report filter is "in the next 90 days", but your formula is "more than 90 days in the future". Try adjusting your formula like so:

=COUNTIFS({Approved Event Date}, AND(@cell >= TODAY(), @cell <= TODAY(90)), {Catering Services Response}, CONTAINS("Yes -", @cell))

Paul Newcome

Try this:

=COUNTIFS({Event Campus}, CONTAINS("Cypress", @cell))

Brian McElligott

Thanks Paul. I don't think I understand the details of the @cell component in a formula. The formula works now but I have some learning to do.

Thanks again.

Paul Newcome

Happy to help. 👍️

@cell basically tells the formula to take the previously established {Range} and evaluate it on a cell by cell basis.

Brian McElligott

Hi Paul,

I have another formula issue with the same components (Contains, and @Cell). When I create a filter / report, my results are as expected (count of 3, or 3 rows returned). When I write the following formula, the result is "1".

Independently, if I look for the first range/criterion, 3 is returned. If I look at the second range/criterion separately, 6 is returned.

Here is the formula where both ranges/criterion are together but 1 is the result.

=COUNTIFS({Approved Event Date}, @cell >= TODAY(+90), {Catering Services Response}, CONTAINS("Yes -", @cell ))

Thoughts?

Paul Newcome

Are you able to provide a screenshot of the report filters?

Brian McElligott

Here is a screen shot of the report filters:

Paul Newcome

Your report filter is "in the next 90 days", but your formula is "more than 90 days in the future". Try adjusting your formula like so:

=COUNTIFS({Approved Event Date}, AND(@cell >= TODAY(), @cell <= TODAY(90)), {Catering Services Response}, CONTAINS("Yes -", @cell))

Brian McElligott

Yep, that totally worked. Studying the @cell and the AND components now. I appreciate the learning!

Paul Newcome

Happy to help. 👍️

Irtaza

@Paul Newcome we meet again lol. I need your advise on the date formula subject. What I am trying to do is that I would like text within the cell to be highlighted. If the end date is 7 days away; it should be highlighted as "Yellow". If the end date passes; then it should be highlighted as "Red" but I would like to use the "% Complete" column and status column to support that formula. how would i be able to do that? Please any help or guidance will be much appreciated.

Paul Newcome

@Irtaza It would depend on the logic you want to use to incorporate the Status and % Complete.

Irtaza

@Paul Newcome thanks Paul for replying; is there an example of the formula you can provide please. I would really appreciate that.

Paul Newcome

@Irtaza I cannot provide an example formula without knowing what your logic is. There are so many different ways to incorporate status and percent complete in relation to a date, I would need more input from you before I could provide anything. And depending on your exact requirements, you may not need a formula. You may be able to accomplish it using just conditional formatting rules.