Countifs

I am trying to use a COUNTIFS formula and it is returning a zero, even though it should return a one...I think it is user error...I checked the reference sheet and it refers to all the columns on the sheet...

=COUNTIFS({SVC Support Priority Range 1}, "[Urgent - Drop Everything]", {SVC Support Priority Range 1}, "In Progress")

Find more posts tagged with

Formulas

Comments

L_123

Did you mean:

=COUNTIFS({SVC Support Priority Range 1}, [Urgent - Drop Everything]@row, {SVC Support Priority Range 1}, "In Progress")

You should always name your other sheet references. It makes troubleshooting much easier.

gwen.pino78261

I tried your formula and I got an error - #unparseable

I am attaching my steps and screen captures to try and better explain thanks

Step 1 -

The "Countifs" image is the sheet Source

Step 2 -

Setting up Formula - Countifs

Range 1 = SVC Support Priority Range 1 – used for both criterion

Includes all columns see below – "Countifs Sheet Reference Name" image

Criterion 1 = {SVC Support Priority Range 1}, "[Urgent - Drop Everything]"

Criterion 2 = {SVC Support Priority Range 1}, "New"

Step 3 -

CountIFS Formula used:

=COUNTIFS({SVC Support Priority Range 1}, "[Urgent - Drop Everything]", {SVC Support Priority Range 1}, "In Progress")
Results are 0 but there is actually 1 that should have been counted see "Countifs_metric" image

L_123

Ok. Couple things.

1. Your references need to just be 1 column, you have many columns referenced as range 1. Your first range should be the entire column Priority. Your second range should be the entire column Status.

*To select an entire column, simply click on the header of the individual column. This will create a dynamic reference that will grow with the sheet.

2. Reference a text value as being in quotations. "Urgent- Drop Everything" is a text value. [Urgent-Drop Everything]1 is the first cell in the column Urgent- Drop everything. Hard brackets mean you are referencing a column.

Your final formula should be the below, but the key to getting it is setting up the references correctly. Again, remember to name them for easier troubleshooting/upgrading down the line.

=COUNTIFS({SVC Support Priority Priority}, "Urgent - Drop Everything", {SVC Support Priority Status}, "In Progress")

gwen.pino78261

Thanks...I updated my naming convention...and selected the individual columns...It worked!

Sign in to join the conversation!

Quick Links

Trending discussions

How to split contents from a cell from a smartsheet into another smartsheet
I need to extract and split the contents of a cell in a Smartsheet into three separate columns to a new smartsheet. The cell entries are originally submitted through a form.
How can I set up recurring tasks in Smartsheet?
I need tasks that repeat (daily, weekly, monthly, yearly). Each should show a due date and a “Completed” checkbox. When I check it off, I’d like the task to log in an archive sheet with the completed date and then reset on the main sheet with a new due date and cleared checkbox. Has anyone found a simple way to do this?
Information in a form populates specific cell.
Hello, Just investigating an idea. I'm building a staff register sheet. I'm curious to know if its possible to populate specific cells by the answers given in a form? So if, in a form, a staff member gives their name, the date and that they will be on leave, sick, training ect. Once the form is submitted, it will populate…