Using COUNTIFS across multiple columns for one criteria

thadr

Hello All,

I hope you are all doing well.

I'm trying to use the Countifs formula by referencing our Portfolio Project sheet. I'm using five columns. One column is the project status which I am limiting to those projects with a status of "In Progress" (Range 1). The other four columns are columns that I titled, Project Component 1, Project Component 2,...Project Component 4 (range 2). The project component columns are drop downs and the values for selection are the same across all the columns. As a PM, I can select Value A in any of the columns. 

This is my formula

=countifs({Portfolio Project sheet range 1}, "In Progress", {Portfolio Project sheet range 2}, "Value A")

range 2 is the range of the four project component columns.

Smartsheet is coming back with #Incorrect Argument Set

As a test, I created the following formula, =countif({Portfolio Project sheet range 2}, "Value A". This brought back a number of how many times "Value A" was selected across the four columns.

When I add the other criteria of "In Progress" and change to countifs, it comes back with the error above. 

It would seem that I would be able to get the information I am requesting since it's able to count using the countif formula and specifying the four columns. 

Thanks in advance.

Chris McKay

HI thadr,

It's because COUNTIFS has trouble dealing with ranges of different sizes. Nice huh? If you change Range 2 to a single column, it will work.

So how do you get around the fact that you need to include one single column range and one four column range? You'll need to create something like this:

=countifs({Portfolio Project sheet range 1}, "In Progress", {Portfolio Project sheet range 2}, "Value A") + countifs({Portfolio Project sheet range 1}, "In Progress", {Portfolio Project sheet range 3}, "Value A") + countifs({Portfolio Project sheet range 1}, "In Progress", {Portfolio Project sheet range 4}, "Value A") + countifs({Portfolio Project sheet range 1}, "In Progress", {Portfolio Project sheet range 5}, "Value A")

Where range 2 is your first dropdown column, range 3 is your second dropdown column etc.

I know it's not ideal, but I hope this helps.

Kind regards,

Chris McKay

thadr

Hi Chris,

Not ideal but it does the trick. Thank you for the quick response. I hope to one day return the favor.

Best,

Thad

Chris McKay

Hi Thad,

My pleasure  .

Kind regards,

Chris McKay

Team Orbie 9

Chris,

In this example could I not make the first range 4 columns wide to make it match the second range? I have a similar situation but my first range is one column and my second is 75 columns. Doing what is shown above is possible but extremely miserable. I did set it up with the 2 75 column ranges and it worked but the second range only seemed to count the first column.

Any help is duly appreciated