Can you nest multiple IF formulas with multiple IF/AND formulas? I have successfully nested several IF formulas and ONE IF/AND formula but I need to add one more IF/AND formula but it is telling me that I have too many arguments.. HELP! What am I doing wrong?

Just imagine how much help we could be if you posted the formula that you are having trouble with. It's kind of hard for us to tell what you are doing wrong if we don't know what you are doing.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

The actual formula is - =IF([@TYPE]='Merch. Solutions'!$C$6,[@['# STORES]]*[@[LOAD-IN]]*6, IF([@TYPE]='Merch. Solutions'!$C$8, [@['# STORES]]*[@[LOAD-IN]]*24, IF([@TYPE]='Merch. Solutions'!$C$10, [@['# STORES]]*[@[LOAD-IN]]*6, IF(AND([@TYPE]='Merch. Solutions'!$C$7,[@PRODUCT]="Winter Apple & Cinnamon SOE"),[@['# STORES]]*[@[LOAD-IN]]*12, [@['# STORES]]*[@[LOAD-IN]]*6))))

I still need to add IF(AND([@TYPE]='Merch. Solutions'!$C$9, [@PRODUCT]="Gingerbread Man", [@['#STORES]]*[@[LOAD-IN]]*24,[@['#STORES]]*[@[LOAD-IN]]*36

But once I add this last part it tells me I have too many arguments..

What application are you using this formula in? It doesn't work in Excel due to invalid characters.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

It is in Excel 2013.. The formula works correctly without that last IF/AND part added..

I have a copy of 2013 but I haven't installed it yet. I am running 2010 and your formula returns the following error: The name that you entered is not valid Reason for this can include: - The name does not being with a letter or an underscore - The name contains a space or other invalid characters - The name conflicts with an Excel built-in name or the name of another object in the workbookWhen I click OK it highlights

[@TYPE]. If I change that to a valid name, such as TYPE, I get the same error but now it highlights[@['#. So it appears that 2010 does not like that syntax.I'll make you a deal: I'll work on trying to figure out why you can't add another IF(AND...) by rewriting you formula so that my Excel will accept it if you'll explain what these syntaxes mean:

[@TYPE]

[@['# STORES]]Thanks!

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

I wonder if that an Excel 2013 thing then? The naming is what excel inputs when I select the cell I'm referencing in the formula.. I believe its because the cells are within a table so the column name over rides the individual cell reference (i.e. G14, etc..) So for example, when I try to build a simple formula such as =A1*B1, those cells are within columns named # Stores and Load-in, so when I click them to build the formula excel is imputing [@[#store]] and [@Load-in]..

Yes, OK, that's it. I created a table (something I don't normally do) and it did indeed use that syntax when I referenced a cell within the table. Where are you trying to add the additional IF(AND...) clause? Can you post the non-working formula?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

The non working formula is: =IF([@TYPE]='Merch. Solutions'!$C$6, [@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$D$6,IF([@TYPE]='Merch. Solutions'!$C$8, [@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$D$8, IF([@TYPE]='Merch. Solutions'!$C$9,[@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$D$9,IF([@TYPE]='Merch. Solutions'!$C$10, [@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$D$10, IF(AND([@TYPE]='Merch. Solutions'!$C$7, [@PRODUCT]="Winter Apple & Cinnamon"),[@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$F$7, [@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$D$7, IF(AND([@TYPE]='Merch. Solutions'!$C$9, [@PRODUCT]="Gingerbread Man"),[@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$F$9, [@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$D$9)))))

It tells me that I have too many arguments.. But if you take out that last IF(AND(... it works.. Grrrr!

Well, after all that work, I just looked at the IF(AND...) that you are trying to add. It appears that you have not closed the parentheses for the AND Function:

I added a close-parenthesis after the "Gingerbread Man"

IF(AND([@TYPE]='Merch. Solutions'!$C$9, [@PRODUCT]="Gingerbread Man"), [@['#STORES]]*[@[LOAD-IN]]*24,[@['#STORES]]*[@[LOAD-IN]]*36Let me know if that helps.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Still no luck.. The last message I sent, the non working formula had the closed parenthesis after "Gingerbread Man"), ... and it still tells me too many arguments..

OK, I think I see the problem, but I'm going to let you test it because I don't have all of your tables nor do I want to try and figure out all of your criteria. Let's break down the last part of the formula, starting with the first IF(AND...) by the arguments:

IF(AND([@TYPE]='Merch. Solutions'!$C$7, [@PRODUCT]="Winter Apple & Cinnamon"), (

logical_test)[@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$F$7,

(value_if_true)[@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$D$7,

(value_if_false)IF(AND([@TYPE]='Merch. Solutions'!$C$9, [@PRODUCT]="Gingerbread Man"), (

logical_test)[@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$F$9,

(value_if_true)[@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$D$9)))))

(value_if_false)If I'm not mistaken, the second IF(AND...) needs to be the

(value_if_false)

for the first IF(AND...)). In other words, you can't close out the first IF(AND...) with a(value_if_false)and then just tack another complete IF function on the end.Does that make sense?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Ok I think I know what you mean, just not sure how to write it... So... If: IF(AND([@TYPE]='Merch. Solutions'!$C$7, [@PRODUCT]="Winter Apple & Cinnamon"),

(logical_test)

[@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$F$7,(value_if_true)IF(AND([@TYPE]='Merch. Solutions'!$C$9, [@PRODUCT]="Gingerbread Man"),

(logical_test)

[@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$F$9,(value_if_true)[@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$D$7,(value_if_false),

[@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$D$9)))))(value_if_false)??? Do I put the FALSE portions at the tail end of the formula?

I don't know your criteria, but typically the last value_if_falseis the result you want if none of the other IF clauses are TRUE.=IF(A1=1, 1, IF(A1=2, 2, IF(A1=3, 3, "A1 Does Not Equal 1, 2 or 3")))

In other words, the 2nd and 3rd IF's are the

value_if_falsearguments for the preceeding IF's and the finalvalue_if_false("A1 Does Not Equal 1, 2 or 3") means that none of the 3 IF's were TRUE.In your case, your original, final

value_if_falsewas [@['# STORES]]*[@[LOAD-IN]]*6. If that's still the finalvalue_if_false, then your new IF(AND...) with it'svalue_if_trueandvalue_if_falseshould go before that.Only you know what you want returned for each condition, so only you can determine which argument goes where.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Not sure what your doing, but see if this helps: =IF([@TYPE]='Merch. Solutions'!$C$6,

[@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$D$6,IF([@TYPE]='Merch. Solutions'!$C$8,

[@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$D$8,IF([@TYPE]='Merch. Solutions'!$C$9,

[@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$D$9,IF([@TYPE]='Merch. Solutions'!$C$10,

[@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$D$10,IF(AND([@TYPE]='Merch. Solutions'!$C$7,[@PRODUCT]="Winter Apple & Cinnamon"),

<< Your AND statement ends with the closed parentheses after Cinnamon. >>

[@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$F$7,<< Should be an IF statement here >>

[@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$D$7,IF(AND([@TYPE]='Merch. Solutions'!$C$9,[@PRODUCT]="Gingerbread Man"),

[@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$F$9,

[@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$D$9)))))MIKE

P.S. When this issue is finally resolved and your IF statement is working, will you be sending us some "Winter Apple & Cinnamon" and "Gingerbread Man" snacks?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Still no luck! :( Is there another way, possibly VLOOKUP (which I also know very little about) that may work better?

What does your new formula look like? Try adding one additional parentheses symbol at the end and see if that helps.

In your old formula, in addition to needing another IF statement,

needed a additional paren.MIKE

We mentioned this a number of times, but I'll mention again - one last time.

Please post your most current formula.We don't know what changes you've, so it's impossible for us to tell you what the problem is. If you don't show us what's not working, how can we possibly offer any suggestions? Each time you've posted your latest attempt, we've pointed out problems.

We need something to work with. "Still no luck!" is not something we can test.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

=IF([@TYPE]='Merch. Solutions'!$C$6, [@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$D$6,IF([@TYPE]='Merch. Solutions'!$C$8, [@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$D$8, IF([@TYPE]='Merch. Solutions'!$C$10, [@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$D$10, IF(AND([@TYPE]='Merch. Solutions'!$C$9,[@PRODUCT]="Gingerbread Man"),[@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$F$9, IF(AND([@TYPE]='Merch. Solutions'!$C$7, [@PRODUCT]="Winter Apple & Cinnamon"),[@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$F$7, [@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$D$9, [@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$D$7))))) Still tells me too many arguments but I'm not confident that this new way I wrote it is correct...

I have no current updated formula because nothing I've tried has worked.. The formula that is functioning right now is: =IF([@TYPE]='Merch. Solutions'!$C$6, [@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$D$6,IF([@TYPE]='Merch. Solutions'!$C$8, [@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$D$8, IF([@TYPE]='Merch. Solutions'!$C$9,[@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$D$9,IF([@TYPE]='Merch. Solutions'!$C$10, [@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$D$10, IF(AND([@TYPE]='Merch. Solutions'!$C$7, [@PRODUCT]="Winter Apple & Cinnamon"),[@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$F$7, [@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$D$7)))))

But I still need to figure out how to add:

IF(AND([@TYPE]='Merch. Solutions'!$C$9, [@PRODUCT]="Gingerbread Man"),[@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$F$9, [@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$D$9)

Your two final IF statements are incompatible, so try this: =IF([@TYPE]='Merch. Solutions'!$C$6,[@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$D$6,IF([@TYPE]='Merch. Solutions'!$C$8,[@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$D$8,IF([@TYPE]='Merch. Solutions'!$C$9,[@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$D$9,IF([@TYPE]='Merch. Solutions'!$C$10,[@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$D$10,IF([@TYPE]='Merch. Solutions'!$C$7,IF(AND([@TYPE]='Merch. Solutions'!$C$7, [@PRODUCT]="Winter Apple & Cinnamon"),[@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$F$7,[@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$D$7),IF(AND([@TYPE]='Merch. Solutions'!$C$9, [@PRODUCT]="Gingerbread Man"),[@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$F$9,[@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$D$9))))))

I've added an additional IF to test whether your working with

'Merch. Solutions'!$C$7

or

'Merch. Solutions'!$C$9Not completely sure it will work.

MIKE

Thank you for the formula, it does not return any error messages but it for some reason does not recognize the IF/AND formulas.. It just ignores them..

I decided to break your original formula down into it's individual clauses to see if that would help. I think I noticed something. Keep in mind that a Nested IF statement stops when it encounters the first IF clause that evaluates to TRUE.

Let's take a look at your 5 original IF clauses and then the clause that you want to add.

1 -IF([@TYPE]='Merch. Solutions'!$C$6, [@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$D$6,

2 -IF([@TYPE]='Merch. Solutions'!$C$8, [@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$D$8,

3 -IF([@TYPE]='Merch. Solutions'!$C$9,[@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$D$9,

4 -IF([@TYPE]='Merch. Solutions'!$C$10, [@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$D$10,

5 -IF(AND([@TYPE]='Merch. Solutions'!$C$7, [@PRODUCT]="Winter Apple & Cinnamon"),[@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$F$7, [@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$D$7)))))

New:IF(AND([@TYPE]='Merch. Solutions'!$C$9, [@PRODUCT]="Gingerbread Man"),[@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$F$9, [@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$D$9)Take a look at #3. If

[@TYPE]='Merch. Solutions'!$C$9is TRUE, then you are never going to get down to the IF(AND...) that you want to add. The formula will return thevalue_if_truefor #3 and then stop.So, even if you can figure out how to add that final IF(AND...) it will never get evaluated if it is added to the end.

The question now is:

Which do you want to take priority - the IF(AND..) that includes [@TYPE]='Merch. Solutions'!$C$9 and [@PRODUCT]="Gingerbread Man") or [@TYPE]='Merch. Solutions'!$C$9 by itself.

Once we have that answer, we can proceed.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Ask Your Question

Weekly Poll

Do you think third-party cookies should be blocked by browsers?

Discuss in The Lounge

Poll History