Countifs #incorrect Argument set

sravya.gupta127326

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!

Genevieve P.

Hi @sravya.gupta127326

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

sravya.gupta127326

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!

Paul Newcome

Try adding the = before Justify$1.

sravya.gupta127326

Thanks Paul - that didn't work...

Genevieve P.

Hi @sravya.gupta127326

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

sravya.gupta127326

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....

Genevieve P.

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

sravya.gupta127326

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!

Genevieve P.

I'm glad you figured it out! 🙂