Incorrect Argument Set

Susan Swisher

Please help: why am I getting this "Incorrect Argument Set"

=SUMIFS([Targeted Area]:[Targeted Area], "Atopic Dermatitis", [Approved 2022 $$]:[Approved 2022 $$], 1)

This formula uses 2 columns [Targeted Area] and [Approved 2022 $$] (which has the $$ approved amount) and says... look at the "Targeted Area" column, if it is this "Atopic Dermatitis" then ADD what is in the column (Approved 2022 $$).

What am I missing? I know it is something simple.

Andrée Starå

Hi @Susan Swisher

I hope you're well and safe!

Try something like this.

=SUMIFS([Approved 2022 $$]:[Approved 2022 $$], [Targeted Area]:[Targeted Area], "Atopic Dermatitis")

Did that work/help?

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, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Susan Swisher

Hi @Kelly Moore .. I saw your response on another question, regarding formula's (SUMIFS) and multiple criteria and thought maybe you could look at my request / question and potentially help. It is driving me crazy and I am not able to fix the problem.

I was able to get a "0" in the return/response with this formula; however, it obviously looking for both instances:

=SUMIFS([Approved 2022 $$]:[Approved 2022 $$], [Targeted Area]:[Targeted Area], "Gastric", [Targeted Area]:[Targeted Area], "Bladder")

Kelly Moore

Hey @Susan Swisher

I am assuming you are wanting this to be an OR, rather than the AND?

=SUMIFS([Approved 2022 $$]:[Approved 2022 $$], [Targeted Area]:[Targeted Area], OR(@cell="Gastric", @cell="Bladder"))

Does this give you what you need?

Kelly

Kelly Moore

Phew! Glad that is all that it was!

Andrée Starå

Hi @Susan Swisher

I hope you're well and safe!

Try something like this.

=SUMIFS([Approved 2022 $$]:[Approved 2022 $$], [Targeted Area]:[Targeted Area], "Atopic Dermatitis")

Did that work/help?

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, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Susan Swisher

YOU @Andrée Starå are a ROCK Star! I knew it was something simple; I was overthinking it!

Thank you so much!

Susan Swisher

@Andrée Starå one additional question: What if the 2nd part of the formula ("Atopic Dermatitis") has multiple criteria? in other words, I want to add ALL of the items that are "Atopic Dermatitis" and "GVHD" and "Pipeline"

=SUMIFS([Approved 2022 $$]:[Approved 2022 $$], [Targeted Area]:[Targeted Area], "Atopic Dermatitis")

How do I string that all together?

Andrée Starå

@Susan Swisher

Excellent!

Happy to help & thanks for the kind words!

Susan Swisher

@Andrée Starå one additional question: What if the 2nd part of the formula ("Atopic Dermatitis") has multiple criteria? in other words, I want to add ALL of the items that are "Atopic Dermatitis" and "GVHD" and "Pipeline"

=SUMIFS([Approved 2022 $$]:[Approved 2022 $$], [Targeted Area]:[Targeted Area], "Atopic Dermatitis")

How do I string that all together?

Susan Swisher

@Andrée Starå one additional question: What if the 2nd part of the formula ("Atopic Dermatitis") has multiple criteria? in other words, I want to add ALL of the items that are "Atopic Dermatitis" and "GVHD" and "Pipeline"

=SUMIFS([Approved 2022 $$]:[Approved 2022 $$], [Targeted Area]:[Targeted Area], "Atopic Dermatitis")

How do I string that all together?

Susan Swisher

Hi @Kelly Moore .. I saw your response on another question, regarding formula's (SUMIFS) and multiple criteria and thought maybe you could look at my request / question and potentially help. It is driving me crazy and I am not able to fix the problem.

I was able to get a "0" in the return/response with this formula; however, it obviously looking for both instances:

=SUMIFS([Approved 2022 $$]:[Approved 2022 $$], [Targeted Area]:[Targeted Area], "Gastric", [Targeted Area]:[Targeted Area], "Bladder")

Kelly Moore

Hey @Susan Swisher

I am assuming you are wanting this to be an OR, rather than the AND?

=SUMIFS([Approved 2022 $$]:[Approved 2022 $$], [Targeted Area]:[Targeted Area], OR(@cell="Gastric", @cell="Bladder"))

Does this give you what you need?

Kelly

Susan Swisher

@Kelly Moore unfortunately it gives me #UNPARSEABLE

=SUMIFS([Approved 2022 $$]:[Approved 2022 $$], [Targeted Area]:[Targeted Area], OR (@cell="Gastric", @cell="Bladder"))

Susan Swisher

@Kelly Moore OH MY... you will NEVER believe (of course you will) what it was... a SPACE in between the OR and the (

that fixed it.. you are GENIUS! Thank you.

Kelly Moore

Phew! Glad that is all that it was!

Susan Swisher

@Kelly Moore one more issue:

Another formula, using COUNTIFS, to count items, if the cell contains a certain "criteria" isn't counting a specific cell because it is a multi-select drop down and it has 2 items in the cell; the 1 it should count and 1 additional item, it shouldn't count:

=COUNTIFS([INCY Compound]:[INCY Compound], "INCB50465", Approved:Approved, 1)

Kelly Moore

Hey Susan

The HAS and the CONTAINS functions help out when dealing with multi-select columns. The HAS is an exact match, CONTAINS is not. When using the two of them, make note the syntax is opposite between them. I always double-check the formula helper to make sure I wrote the correct one.

Let's try this

=COUNTIFS([INCY Compound]:[INCY Compound], HAS(@cell, "INCB50465"), Approved:Approved, 1)

Does this work for you?

Kelly

Susan Swisher

@Kelly Moore That worked perfectly. I am not sure I know how to use the "formula helper" and I don't fully understand the @cell yet.

Thank you.

Kelly Moore

Great questions!

When you begin typing a formula, a small window (for lack of a better term) appears. The helper is showing the function you are working with, and the highlighted text moves and shows you what element of the function that smartsheet things you are currently at. For instance, if you think you are inserting a criteria for a Nested IF, but the helper is showing that you are in the 'true' element of the IF, then you have a comma or parenthesis out of place.

In the CONTAINS screenshot above I have moved the cursor past the search term (search_for) and the helper is indicating I need to designate the location (search_within). If you hover in some of your formula cells and move through the formula, you will see the highlighted text change.

The @cell tells a formula that as you go into a range, evaluate each cell one by one. In your COUNTIFS formula you designated the range as your [INCY Compound] column. The column doesn't equal your criteria - you are looking for the cells within you column that equals the criteria. The @cell tells the formula that's what you want. Note you have already told it which @cells you want - you want the ones in the [INCY Compound] column.

IF that doesn't make sense, ask me again and maybe someone can explain it more eloquently. Questions are good. Keep asking.

Kelly