Using COUNTIFS with ranges of different size
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)
Best Answer
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Answers
-
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
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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
-
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)
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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.
-
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?
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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.
-
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,
GenevieveNeed more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!