Countifs drop-down multi columns

Hi,

I'm unable to get countifs results on this sheet where GEO is drop-down list column allowing for multi values and STATUS is a drop-down list allowing single values.

I'm looking to count the number of values GEO if the STATUS is COMPLETE.

In this case:

EU, COMPLETE (2)

US, COMPLETE (1)

AFR, COMPLETE (1)

Any idea how to solve this?

Thanks

Best Answer

  • KPH
    KPH ✭✭✭✭✭✭
    Answer βœ“

    So close @francesco !

    If you use CONTAINS and @cell rather than HAS and a range, and treat the single select dropdown as a normal text string, you will achieve what you want.🀞

    =COUNTIFS(Geo1:Geo3, CONTAINS("EU", @cell), Staus1:Staus3, "COMPLETE")

Answers

  • francesco
    francesco ✭✭

    Trying along this path =COUNTIFS(Geo1:Geo3, HAS("EU", Geo1:Geo3), Status1:Status3, HAS(Status1:Status3, "COMPLETE")) but the result is always 0

  • KPH
    KPH ✭✭✭✭✭✭
    Answer βœ“

    So close @francesco !

    If you use CONTAINS and @cell rather than HAS and a range, and treat the single select dropdown as a normal text string, you will achieve what you want.🀞

    =COUNTIFS(Geo1:Geo3, CONTAINS("EU", @cell), Staus1:Staus3, "COMPLETE")

  • francesco
    francesco ✭✭

    Awesome @KPH . Many thanks! This would work fine also if I'm reporting the counts into another sheet, do you know? Thank you!

  • KPH
    KPH ✭✭✭✭✭✭

    Yes, it will work across sheets. Just replace the column references (the bold parts here)

    =COUNTIFS(Geo1:Geo3, CONTAINS("EU", @cell), Staus1:Staus3, "COMPLETE")

    with the cross sheet references.

    Select the reference to change and a pop-up will help you create the cross-sheet reference.


    Click Reference Another Sheet.

    Find your sheet in the tree.

    Highlight the column.

    Give it a name (here I use Geo Col).

    Submit and repeat for Status.


    Your new formula will look like this.

    =COUNTIFS({Geo Col}, CONTAINS("EU", @cell), {Status Col}, "COMPLETE")

    Whatever names you chose when setting up the references will replace the names in bold.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!