Using COUNTIFS across multiple columns for one criteria

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.

Find more posts tagged with

Formulas

Project Management

Comments

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

Sign in to join the conversation!

Quick Links

Trending discussions

Match or Index
I am trying to pull a phone number and email from our master roster that is on another sheet in Smartsheet. I cannot figure out the formula. This is the formula I have tried. =INDEX([EMPLOYEE NAME]@row , MATCH({IMS Master Roster Range 2}), 0)
Workflow Generate Impact Report PDF in Impact Form Response Sheet has become invalid
Hi All, I keep getting this annoying error when I try to trigger a PDF generation. I initially set up to trigger the PDF generation to kick off once the last field the needs data is "not blank". That is when the error started. I modified the sheet and added a yes/no drop down to trigger the PDF generation when the value in…
INDEX COLLECT MIN
Desired result is to pull the Initiative Name where the Days to Due Date is the least (so, due the soonest); then to make an index for the Top 10 Initiatives Due Soonest Currently using this formula: =INDEX(COLLECT({Project Master - Initiative}, {Project Master - Days to Due}, MIN({Project Master - Days to Due})), 1) This…