Counting rows with a checkbox

Jeff_ARC

Hi I am trying to count the number of records that meet a certain criteria and has checked checkbox.

The formula I am trying is:

=COUNTIFS({Evaluation}, "1", {Resources}, CONTAINS("Scenario 2", @cell))

{Evaluation} is a column that uses check boxes

{Resources} is a column that uses dropdown selections

I want to count the number of times the evaluation checkbox is checked and Resources contains the phrase "Scenario 2".

If I separate each of the statements I get the appropriate information but when I am trying to use COUNTIFS with more than one range and criteria I am getting the error message ##Incorrect Argument Set

JamesB

@Jeff_ARC

Try taking the 1 out of quotations.

=COUNTIFS({Evaluation}, 1, {Resources}, CONTAINS("Scenario 2", @cell))

Jeff_ARC

Still have the same issue

JamesB

@Jeff_ARC

I would try the HAS formula

=COUNTIFS({Evaluation}, 1, {Resources},HAS(@cell, "Scenario 2"))

Another option is to break down your formula and test each COUNTIF individually first to see which one is erroring the return.

=COUNTIF({Evaluation}, 1)

=COUNTIF({Resources},HAS(@cell, "Scenario 2"))

Then you can rebuild it back to a COUNTIFS.

Jeff_ARC

I can't use the HAS function because {Resources} has a lot of text so I am only looking if "Scenario 2" exists within the text.

When I break down into the two COUNTIF structures I get the correct results when I combine them into the COUNTIFS I am getting the error.

JamesB

@Jeff_ARC

See the following post from 2019. Paul Newcome who is a great resource when it comes to formulas provides insight to issues he has seen when using the CONTAINS formula. Possibly this will help with the issue you are seeing.

https://community.smartsheet.com/discussion/64461/countifs-contains-function