Using Countifs, but column name has 2 words + Count will not work if 2 Items Exist in same cell

JamesS

I'm trying to count how many rows with "XYZ Weekly" in a column called "Item Type" that have "Open" in a column called "Status":

Item Type column has a Dropdown list: XYZ Weekly, ABC Daily, Agenda

Status column has a Dropdown list: Open, Closed, On-Hold

This is the formual I'm trying to use:

=COUNTIFS(Item Type:Item Type, "XYZ Weekly", Status:Status, "Open")

I get UNPARSABLE unless I rename the column to "ItemType" (removing the space, also from the formula)

Q1. Must I remove the space, I would prefer not too?

Also, the count does not work, if XYZ Weekly & Agenda are present in the same cell of "Item Type".

Q2. Is there a way to count if XYZ Weekly is present, regardless of any other content in the cell?

Thanks in advance for any assistance 😎

AKnight

Hi @JamesS !

Sorry about that, I messed up the range at the beginning! I was rushing writing the formula between meetings. Try this instead:

=COUNTIFS([Item Type]:[Item Type], HAS(@cell, "GDS Weekly), Status:Status, "Open")

AKnight

Hi @JamesS !

When your columns have a space, format your formula with brackets around the column name. It would look like :

=COUNTIFS([Item Type]:[Item Type], "XYZ Weekly", Status:Status, "Open")

Because the dropdown in item type is multiselect, you will need to include HAS because the formula will need to look for one item among a list of items.

I would try for your final formula:

=COUNTIFS(HAS([Item Type]:[Item Type], "XYZ Weekly"), Status:Status, "Open")

Hope this helps! Let me know if your column type is different than what I assumed and I can help accordingly fix the formula.

JamesS

Hi @AKnight ,

Solved the column with a space issue - thank you 😊

But I'm getting error #INCORRECT ARGUMENT SET when I use the following:

=COUNTIFS(HAS([Item Type]:[Item Type], "GDS Weekly"), Status:Status, "Open")

Any thoughts?

AKnight

Hi @JamesS !

Sorry about that, I messed up the range at the beginning! I was rushing writing the formula between meetings. Try this instead:

=COUNTIFS([Item Type]:[Item Type], HAS(@cell, "GDS Weekly), Status:Status, "Open")

JamesS

Thanks @AKnight

That work nicely 😊👍️