COUNTIFS Referenced Ranges - Incorrect Argument Set

Jacob Rossi

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

KPH

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

Devin Lee

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

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

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

@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?

Jacob Rossi

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

@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.