COUNTIFS adding checkbox column returns "#INCORRECT ARGUMENT SET"

jonnyp

I'm using a COUNTIFS statement to sum up specific conditions. I have it functioning correctly aside from not being able to incorporate an "Active" column, which is a formula applied to a column that checks the box (true/false) based on a separate condition. When I attempt to add this final condition, I encounter the following error "#INCORRECT ARGUMENT SET". If I add the formula pertaining to the "Active" column in isolation on a different row (=COUNTIF(Active@row, true)), it functions just fine. Incorporating both is leaving me stumped. My existing formula is below. I appreciate any feedback you can provide. Thank you!

Working formula: =COUNTIFS({End Dates}, <TODAY(), {End Dates}, <>"", {TP Docs}, =$Document2, {TP Docs}, <>"", {Task}, NOT(CONTAINS("progress", @cell)))

Non working: =COUNTIFS(Active@row, =true, {End Dates}, <TODAY(), {End Dates}, <>"", {TP Docs}, =$Document2, {TP Docs}, <>"", {Task}, NOT(CONTAINS("progress", @cell)))

-JP

DesireeJones

Hi @jonnyp ,

In the non-working formula, the "=" before "true" could be the error. Try removing the equal sign and see if that works. See below:

=COUNTIFS(Active@row, true, {End Dates}, <TODAY(), {End Dates}, <>"", {TP Docs}, =$Document2, {TP Docs}, <>"", {Task}, NOT(CONTAINS("progress", @cell)))

jonnyp

Hi @DesireeJones,

I've tried it both ways, with and without the equal sign. I'm still getting the same error. Keep the ideas coming!

DesireeJones

Another solution I can think of is separating the formula and connecting the sections with "+". See below:

=COUNTIF([Active]@row, true) + COUNTIF({End Dates}, <TODAY()) + COUNTIF({End Dates}, <>"") + COUNTIF({TP Docs}, $Document2) + COUNTIF({TP Docs}, <>"") + COUNTIF({Task}, NOT(CONTAINS("progress", @cell)))

jonnyp

This doesn't throw an error, but the value returned isn't correct. I suspect it's doing a count of each of the scenarios listed and essentially summing them vs a count of when ALL conditions are occurring simultaneously.

DesireeJones

The value is probably incorrect because now there are 2 COUNTIF formulas for {End Dates} and {TP Docs}.

For {End Dates},< TODAY(), the formula is counting the end dates that are in the past from today's date.

For {End Dates}, <>"" , the formula is counting anything in that column.

For {TP Docs}, $Document2, the formula is counting that reference.

For {TP Docs}, <>"", the formula is anything in that column.

Is that all what you want to count? We could try to throw a OR statement in there. Do you mind sharing a screenshot of your sheet without any confidential information to get a better idea of what your references look like?

jonnyp

I need a count for when all the scenarios exist simultaneously. An "OR" statement wouldn't capture the correct criteria.

{End Dates} references a column to capture projected end dates for a line item.

{TP Docs} references a column to capture the document type and compare it to a list of actively in development documents. Understanding this condition will let me know whether to track and include a count of these dates in an overall total, but the only way to do that is to interrogate whether it's active or not.

DesireeJones

Okay so the following formula counts the number of occurrences if active is true (checked):

=IF([Active]@row = true,  COUNTIFS({End Dates}, <TODAY(), {End Dates}, <>"") + COUNTIF({TP Docs}, $Document2, {TP Docs}, <>"") + COUNTIF({Task}, NOT(CONTAINS("progress", @cell)))

Did you want to count the active row too?

=COUNTIF([Active]@row, true) + COUNTIFS({End Dates}, <TODAY(), {End Dates}, <>"") + COUNTIF({TP Docs}, $Document2, {TP Docs}, <>"") + COUNTIF({Task}, NOT(CONTAINS("progress", @cell)))

What result are you expecting if all criteria matches?

jonnyp

So this is closer to what I want. I don't know why I wasn't using the COUNTIFS in a nested IF statement on the "Active" flag.

When "Active" is false, it doesn't do the calculation - this is good.

When "Active" is true, I'm still getting #INCORRECT ARGUMENT SET.

I feel like we're getting closer. I'll keep trying to play around with it as well. Thanks for your help so far!

DesireeJones

There was an extra space after the IF statement in the beginning and a missing "S" in the second COUNTIFS :)

Try this:

=IF([Active]@row = true, COUNTIFS({End Dates}, <TODAY(), {End Dates}, <>"") + COUNTIFS({TP Docs}, $Document2, {TP Docs}, <>"") + COUNTIF({Task}, NOT(CONTAINS("progress", @cell)))

jonnyp

@DesireeJones, I finally got it!

The multiple + were still not returning the unique scenario where all conditions were true and therefore expanding the actual cases. Just needed to clean that up using the initial IF statement. Thank you so much for helping me get on the right track!

For anyone else following along, here's the working version:

=IF(Active@row = true, (COUNTIFS({End Dates}, <TODAY(), {End Dates}, <>"", {TP Docs}, Document@row, {TP Docs}, <>"", {Task}, NOT(CONTAINS("progress", @cell)))), "Not Active")