Countifs

gwen.pino78261

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")

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!