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)

Tags:

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    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

Answers

  • Bassam Khalil
    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 : [email protected]

    PMP Certified

    [email protected]

    ☑️ 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"

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    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å
    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!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • @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.
    Genevieve P. Employee Admin
    edited 08/05/21

    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)

  • 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.
    Genevieve P. Employee Admin

    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?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!