Using COUNTIFS with ranges of different size

Stéphane Charbonneau

COUNTIFS( range1, criterion1, [ range2, ​criterion2​... ])

OK, I'm stuck. When using COUNTIFS, and both ranges only cover 1 column each, or both ranges cover multiple column, it works perfectly.

But, when for example, range 1 covers only 1 column and range 2 covers more than 1 column, I get the following error message: #INCORRECT ARGUMENT SET.

Anyboby knows how to work around this problem? THANKS!

Here is my actual formula:

=COUNTIFS({site}, @cell = Site$1, {obs}, @cell = $Critère@row, {titre}, @cell = PAB$1)

Genevieve P.

Hi @Stéphane Charbonneau

You are correct! Ranges within the same formula need to be the same size. The Help Article for Formula Error Messages (see here) outlines this:

"For functions that take two ranges: The range sizes don’t match for the function."

Can you clarify what it is you're looking to COUNT in your formula?

What are the following ranges:

{site} / {obs} / {titre}

I would suggest each of these should only be one column. If you're searching for the same value in two columns you'll want to either list the criteria twice (once for each column) or add two COUNTIFS statements together.

Cheers,

Genevieve

Bassam Khalil

Hi @Stéphane Charbonneau

Hope you are fine, Please add a copy of your sheet or a screenshot (after removing or replacing any sensitive information). This will make it easier for me to provide the appropriate answer to your question.

Or if you like to fix the formula directly on your sheet please share me as an admin on a copy of your sheets ( Source & Destination ) and i will write the exact formula for you then you can copy it to your original sheet.

My Email for sharing : Bassam.k@mobilproject.it

Genevieve P.

Hi @Stéphane Charbonneau

You are correct! Ranges within the same formula need to be the same size. The Help Article for Formula Error Messages (see here) outlines this:

"For functions that take two ranges: The range sizes don’t match for the function."

Can you clarify what it is you're looking to COUNT in your formula?

What are the following ranges:

{site} / {obs} / {titre}

I would suggest each of these should only be one column. If you're searching for the same value in two columns you'll want to either list the criteria twice (once for each column) or add two COUNTIFS statements together.

Cheers,

Genevieve

Andrée Starå

Hi @Stéphane Charbonneau

I hope you're well and safe!

It won't work because the ranges have to match.

Make sense?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Stéphane Charbonneau

@Genevieve P.

Thanks. Is there anyway I could work around this limitation, maybe using COLLECT or something else?

My problem is that I'm using a form to collect multiple (let's say 5) answers to the same question using a drop down box (for example "present", "not present", "not applicable"), hence the {obs} range (obs stands for "observation"). For each form submitted I therefore have up to 5 observations BUT only one column (drop down box on the form) identifies the "site" ({site}) where these observations have been made.

On a results sheet, I'm trying to count the number of each values ("present", "not present", "not applicable") for each sites.

Thanks

Genevieve P.

Hi @Stéphane Charbonneau

No problem! In this instance you'll want to add together multiple COUNTIFS statements, like so:

=COUNTIFS({site}, @cell = Site$1, {obs - 1}, @cell = $Critère@row, {titre}, @cell = PAB$1)

+ COUNTIFS({site}, @cell = Site$1, {obs - 2}, @cell = $Critère@row, {titre}, @cell = PAB$1)

+ COUNTIFS({site}, @cell = Site$1, {obs - 3}, @cell = $Critère@row, {titre}, @cell = PAB$1)

... etc. Does that make sense?

The full formula would look something like this:

=COUNTIFS({site}, @cell = Site$1, {obs - 1}, @cell = $Critère@row, {titre}, @cell = PAB$1) + COUNTIFS({site}, @cell = Site$1, {obs - 2}, @cell = $Critère@row, {titre}, @cell = PAB$1) + COUNTIFS({site}, @cell = Site$1, {obs - 3}, @cell = $Critère@row, {titre}, @cell = PAB$1) + COUNTIFS({site}, @cell = Site$1, {obs - 4}, @cell = $Critère@row, {titre}, @cell = PAB$1) + COUNTIFS({site}, @cell = Site$1, {obs - 5}, @cell = $Critère@row, {titre}, @cell = PAB$1)

Stéphane Charbonneau

Thank you. I know about this work around but I was trying to find another way to do the same thing. The problem with multiple COUNTIFS is that I end up with long formulas that I need to reproduce for over 200 sites because of the limitation vs the size of sheets.

thanks again.

Genevieve P.

Ah, I see. Another alternative would be to add three hidden helper columns to your source sheet.

One column would count how many values are "present" in that row, the other would count how many are "not present, and the third would minus the sum of these two cells from 5 for your total "not applicable".

You can hide these columns in the main source sheet. Then instead of a COUNTIFS formula, you would use a SUMIFS formula looking at these columns as the single range. Would this be better for your processes?

Eliana Davalos

I too have the same issue with trying to count different ranges.

Without being a programmer myself, it seems like it should be a super easy thing for Smartsheet to be able to count any range a user selects regardless of whether the ranges are or not the same size.

I for example am trying to count how many row entries actually have minutes associated with them on a mock timestamp Smartsheet, that is divided horizontally by weekday parent rows that have formulas at the parent level, reason I cannot just select an entire undivided range straight down.

Here is the formula that is giving me the #INCORRECT ARGUMENT SET, likely due to the different range sizes:

=COUNTIFS([Job Cost Hrs]2039:[Job Cost Hrs]2054, >0, [Job Cost Hrs]2056:[Job Cost Hrs]2105, >0, [Job Cost Hrs]2107:[Job Cost Hrs]2127, >0, [Job Cost Hrs]2129:[Job Cost Hrs]2133, >0)

I know I can probably make the smaller ranges match the biggest range, however, I feel that is not efficient at all for such a simple task.

Anything your team can do to help us with a more versatile COUNTIFS function, would be of tremendous help to its users.

Thank you! ED.

Genevieve P.

Hi @Eliana Davalos

With a COUNTIFS, I like to think of the comma between one set of Range and Criteria as an "AND" statement, regarding the same row (like in a Filter).

This is why your specific formula is giving an error - there would be no situation where row 2039 is greater than 0 at the same time as row 2056 in the same row is giving a 0, since it's on a different row.

Try this instead:

=COUNTIF([Job Cost Hrs]2039:[Job Cost Hrs]2054, >0) + COUNTIF([Job Cost Hrs]2056:[Job Cost Hrs]2105, >0) + COUNTIF([Job Cost Hrs]2107:[Job Cost Hrs]2127, >0) + COUNTIF([Job Cost Hrs]2129:[Job Cost Hrs]2133, >0)

Cheers,
Genevieve