Countifs #incorrect Argument set

Options
✭✭✭✭✭

Hi,

please can someone help on this formula?

Program Site Range - A different sheet with multi drop down column sites

Program Phase Range - A different sheet with single drop down column

=COUNTIFS({Program Site Range}, CONTAINS(\$Metric@row, @cell), {Program Phase Range}, Justify\$1)

If I remove the Phase criteria, it works but I need both conditions. Using Collect is giving just 1 as an answer

In the same sheet, I have these formulae working fine:

=COUNTIFS({Program Summary - Portfolio}, \$Metric@row, {Program Phase Range}, Justify\$1)

=COUNTIFS({Program Summary Sheet | Function}, CONTAINS(\$Metric@row, @cell), {Program Phase Range}, =Justify\$1)

Thank you!

Thanks,

Sravya

Best Answers

• Employee Admin
Answer ✓
Options

Cross-sheet ranges within a formula like this need to be pointing to the same sheet. The Function won't be able to compare rows between two separate sheets for two criteria, because it's looking for the criteria in the same row.

Try adding two complete COUNTIF statements together with a + between them instead:

=COUNTIF({Program Site Range}, CONTAINS(\$Metric@row, @cell)) + COUNTIF({Program Phase Range}, Justify\$1)

You also noted that you're using a Multi-Select dropdown. In this instance, you'll want to use the HAS function instead of CONTAINS. Try:

=COUNTIF({Program Site Range}, HAS(@cell, \$Metric@row)) + COUNTIF({Program Phase Range}, Justify\$1)

Let me know if this works!

Cheers,

Genevieve

• ✭✭✭✭✭
Answer ✓
Options

Hi all, thank you for your comments and thanks to @amy from Smartsheet team - looking at the manage references - I did use a separate sheet for one of the references and hence was not working when I added 2 criteria!

Thanks,

Sravya

Answers

• ✭✭✭✭✭✭
Options

Try adding the = before Justify\$1.

• ✭✭✭✭✭
Options

Thanks Paul - that didn't work...

Thanks,

Sravya

• Employee Admin
Answer ✓
Options

Cross-sheet ranges within a formula like this need to be pointing to the same sheet. The Function won't be able to compare rows between two separate sheets for two criteria, because it's looking for the criteria in the same row.

Try adding two complete COUNTIF statements together with a + between them instead:

=COUNTIF({Program Site Range}, CONTAINS(\$Metric@row, @cell)) + COUNTIF({Program Phase Range}, Justify\$1)

You also noted that you're using a Multi-Select dropdown. In this instance, you'll want to use the HAS function instead of CONTAINS. Try:

=COUNTIF({Program Site Range}, HAS(@cell, \$Metric@row)) + COUNTIF({Program Phase Range}, Justify\$1)

Let me know if this works!

Cheers,

Genevieve

• ✭✭✭✭✭
Options

Thanks Genevieve - Sorry, both phase and site are in the same sheet - so, I need to find a row that has both matching.

Changing to Has did not work....

Thanks,

Sravya

• Employee Admin
Options

Thank you for clarifying!

If it's the same sheet, then the HAS would have been my next suggestion.

=COUNTIFS({Program Site Range}, HAS(@cell, \$Metric@row), {Program Phase Range}, Justify\$1)

When you say it doesn't work, are you receiving an incorrect number or an error? This will only return a count if the Site Range has the selection that's in your Metric column and the Phase has the data from the first row in your Justify column. Is it possible that there is only one row matching these two criteria? Can you apply a Filter on the source sheet to check the same data?

It would be helpful to see screen captures of both sheets, identifying the columns being looked into and the values being used as criteria, but please block out any sensitive data.

Thanks!

Genevieve

• ✭✭✭✭✭
Answer ✓
Options

Hi all, thank you for your comments and thanks to @amy from Smartsheet team - looking at the manage references - I did use a separate sheet for one of the references and hence was not working when I added 2 criteria!

Thanks,

Sravya

• Employee Admin
Options

I'm glad you figured it out! 🙂

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!