COUNTIFS Referenced Ranges - Incorrect Argument Set

Options

Hello,

I have a simple formula that just checks of a column if it finds at least one match. I've used this many times but in this case it's returning Incorrect Argument Set.

Get {Well Plans | Contract} is a text column that is manually entered. {Get Well Plans | Product} is a text column that is a formula that returns one of 4 options depending on a different column in the sheet. If I just count based on the contract or the product, it works fine, but together it always throws this error. From research it suggests the ranges are different but I'm simply choosing the whole column from the same sheet as the other reference.

=IF(COUNTIFS({Get Well Plans | Contract}, [Contract #]@row, {Get Well Plans | Product}, [Product Line]@row) > 0, 1, 0)

Any suggestions on what to look into would be helpful

Answers

  • KPH
    KPH ✭✭✭✭✭✭
    Options

    Formula looks good. I agree with what you found - the range size and shape. Can you try creating new ranges from scratch?

  • Devin Lee
    Devin Lee ✭✭✭✭✭
    Options

    The "Incorrect Argument Set" error in Smartsheet usually indicates an issue with the ranges or references used in your formula. Here are a few things to check and potential solutions:

    1. Range Consistency: Ensure that the ranges for {Get Well Plans | Contract} and {Get Well Plans | Product} cover the same number of rows. Both ranges must be the same length for the COUNTIFS function to work correctly.
    2. Reference Errors: Double-check that {Get Well Plans | Contract} and {Get Well Plans | Product} are correctly referenced and that there are no typos or discrepancies in the column names.
    3. Data Types: Ensure that both columns contain the same data type. If one column is formatted differently (e.g., one is text and the other is a formula that returns text), this might cause issues.

  • Jacob Rossi
    Options

    I've recreated my references multiple times and I'm simply selecting the column header to select the range. I guess it has to be #3 then. I'm not sure I understand why this would cause an issue when you can make it compare these things individually:

    These two work just fine.
    =IF(COUNTIFS({Get Well Plans | Contract}, [Contract #]@row) > 0, 1, 0)
    =IF(COUNTIFS({Get Well Plans | Product}, [Product Line]@row) > 0, 1, 0

    I might reach out to support to see if they can determine exactly why these two together throw that error.

  • heyjay
    heyjay ✭✭✭✭✭
    Options

    Have you tried to check the formula without the IFstatement if it will return a number value?

    =COUNTIFS(
    {Get Well Plans | Contract}, [Contract #]@row, 
    {Get Well Plans | Product}, [Product Line]@row
    )
    

    ...

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    Options

    @Jacob Rossi

    So I have a simple question. The way you are counting. It is checking to see if both Criteria are correct. So that said would {Get Well Plans | Contract} and {Get Well Plans | Product} also have data that matches both the rows your using to count?

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

  • Jacob Rossi
    Options

    I don't understand why, but it's suddenly now working. I haven't changed anything and now it returns results just fine. Thank you everyone for your help on this. I just wish I knew why it struggled. Maybe it just needed the weekend to sort itself out.

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    Options

    @Jacob Rossi I have noticed some times when doing a countif or sumif the sheet needs to be saved and refreshed before the cross sheet formulas actually work. This may have been one of those situations.

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!