COUNTIFS multiple criterion

DariusUsk

Hi All,

Maybe somebody could help me to figure how does COUNTIFS formula works?

I am trying to get summed cells which has specific naming from the drop down.

I want to count "Dismounting" cells only if Column "Done" is not checked.

At the moment I am typing formula in this way:

=COUNTIFS({Type}; "Dismounting"; {Done}; "0")

But I'm getting an error:

INCORRECT ARGUMENT SET

Please help me

Thanks in advance!

Andrée Starå

Hi Darius,

Do you want to count or sum the values?

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Paul Newcome

Try removing the quotes from around the zero. If that still does not work, replace the zero with "false" (not including the quotes).

Andrée Starå

Paul,

Good catch! 

Paul Newcome

Thanks Andree.

 

I use a lot of checkboxes in calculations.

 

I am regularly asked for detailed counts of things, so I generally have more complex calculations broken out into banks of checkboxes. 

 

I personally find it easier to run COUNTIFS on those, and it makes it more straight forward when building reports, filters, and conditional formatting. I can name the column whatever it is that I am calculating and then that will be the range/option I select for those.

.

Header?

Parent?

Early?

Late?

Report?

Metrics?

Current Year?

.

When it comes time to use them in reports or conditional formatting or filters it's as simple as specifying whether or not the box is checked.

DariusUsk

Hi Andree,

For now just to count the values.

Another report will need to sum the values.

I tried to remove quotes and replace it with "false", I still get the same #INCORRECT ARGUMENT SET error.

Thanks!

DariusUsk

Hi!

I think I figured it out why it doesn't work.

Formula is getting confused because of the empty rows.

As if you are not filling any cell in the row with text/numbers/etc., checkbox is not appearing.

Do you think this might be the issue?

Thanks!

Andrée Starå

Ok.

Are the ranges the same?

#INCORRECT ARGUMENT SET

Cause

This error is presented under the following circumstances:

1. For functions that take two ranges: The range sizes don’t match for the function.
2. The function is missing an argument.
3. There is an extra function in the argument.

Resolution

Correct the range size or arguments, adding or removing arguments in the formula.

Hope that helps!

Best,

Andrée

Andrée Starå

No, that's not the issue!

The check boxes or anything else isn't appearing because the rows are not activated (not used)

Hope that helps!

Best,

Andrée

DariusUsk

The formula works OK only if in the range I choose filled cells, choosing whole column gets me the error message.

Thanks for trying to help

EDIT

All works, thank you all for the help!

Andrée Starå

Excellent!

Happy to help!

Best,

Andrée

Paul Newcome

Try breaking it down into two separate formulas to see if one gives the error and the other doesn't. We may be able to trouble shoot this so that you can reference entire columns. That way you don't have to keep going in and updating the range every time a new row is added.

 

=COUNTIFS({Type}; "Dismounting")

=COUNTIFS({Done}; 0)

Andrée Starå

Paul,

He already solved it! 

The formula works OK only if in the range I choose filled cells, choosing whole column gets me the error message.

Thanks for trying to help

EDIT

All works, thank you all for the help!

Paul Newcome

Oops. Missed the "EDIT". Haha. 

Andrée Starå

Haha! Easy to miss!