Countifs drop-down multi columns

francesco

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

KPH

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

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

KPH

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

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

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.