Multiple IF Statment not working

  • Thread starter Thread starter Mark D
  • Start date Start date
M

Mark D

Morning all

I thought the following would be quite simple but I am having trouble with
it. Only the first part of the forumla appears to be working (if g4 = all
then the value I want comes up), howerver if I enter any of the others I get
''FALSE'' appear

Any ideas?

Also if anyone can additionally help me I need to add 1 more IF to this
forumla but it wont let me as I would then have more than 7 functions. So
would anyone have an idea how to reduce this forumla ?

Thanks in advance

=IF(G4="All",COUNT('NA Actuals & Base Info 2009 $'!C3:C36,'EU Actuals & Base
info 2009 €'!C9:C48,'EU Actuals & Base info 2009
€'!C59:C73,IF(G4="London",COUNT('EU Actuals & Base info 2009
€'!C22:C48,IF(G4="Stuttgart",COUNT('EU Actuals & Base info 2009
€'!C59:C73,IF(G4="Geneva",COUNT('EU Actuals & Base info 2009 €'!C9:C21))))))))
 
Try this…

=IF(G4="ALL",COUNT('[NA Actuals & Base Info 2009 $.xls]NA Actuals & Base
Info 2009'!$C$3:$C$36)+COUNT('EU Actuals & Base info 2009
€'!C9:C48)+COUNT('EU Actuals & Base info 2009
€'!C59:C73),IF(G4="LONDON",COUNT('EU Actuals & Base info 2009
€'!C22:C48),IF(G4="STUTTGART",COUNT('EU Actuals & Base info 2009
€'!C59:C73),IF(G4="GENEVA",COUNT('EU Actuals & Base info 2009
€'!C9:C21),""))))

Only 4 If statements are there in the above formula so you can add another 3
more If statements on it…

If this post helps, Click Yes!
 
Perfecto

Thanks very much

Ms-Exl-Learner said:
Try this…

=IF(G4="ALL",COUNT('[NA Actuals & Base Info 2009 $.xls]NA Actuals & Base
Info 2009'!$C$3:$C$36)+COUNT('EU Actuals & Base info 2009
€'!C9:C48)+COUNT('EU Actuals & Base info 2009
€'!C59:C73),IF(G4="LONDON",COUNT('EU Actuals & Base info 2009
€'!C22:C48),IF(G4="STUTTGART",COUNT('EU Actuals & Base info 2009
€'!C59:C73),IF(G4="GENEVA",COUNT('EU Actuals & Base info 2009
€'!C9:C21),""))))

Only 4 If statements are there in the above formula so you can add another 3
more If statements on it…

If this post helps, Click Yes!

--------------------
(Ms-Exl-Learner)
--------------------



Mark D said:
Morning all

I thought the following would be quite simple but I am having trouble with
it. Only the first part of the forumla appears to be working (if g4 = all
then the value I want comes up), howerver if I enter any of the others I get
''FALSE'' appear

Any ideas?

Also if anyone can additionally help me I need to add 1 more IF to this
forumla but it wont let me as I would then have more than 7 functions. So
would anyone have an idea how to reduce this forumla ?

Thanks in advance

=IF(G4="All",COUNT('NA Actuals & Base Info 2009 $'!C3:C36,'EU Actuals & Base
info 2009 €'!C9:C48,'EU Actuals & Base info 2009
€'!C59:C73,IF(G4="London",COUNT('EU Actuals & Base info 2009
€'!C22:C48,IF(G4="Stuttgart",COUNT('EU Actuals & Base info 2009
€'!C59:C73,IF(G4="Geneva",COUNT('EU Actuals & Base info 2009 €'!C9:C21))))))))
Click to expand...
Click to expand...
 
Don't forget that the 7 is a limit on "Nested levels of functions", not just
on nested IF functions. Fortunately, in that formula the COUNT functions
are generally in a different branch of the tree from the extra IF, so it is
not nested to much more deeply.
--
David Biddulph

Ms-Exl-Learner said:
Try this.

=IF(G4="ALL",COUNT('[NA Actuals & Base Info 2009 $.xls]NA Actuals & Base
Info 2009'!$C$3:$C$36)+COUNT('EU Actuals & Base info 2009
?'!C9:C48)+COUNT('EU Actuals & Base info 2009
?'!C59:C73),IF(G4="LONDON",COUNT('EU Actuals & Base info 2009
?'!C22:C48),IF(G4="STUTTGART",COUNT('EU Actuals & Base info 2009
?'!C59:C73),IF(G4="GENEVA",COUNT('EU Actuals & Base info 2009
?'!C9:C21),""))))

Only 4 If statements are there in the above formula so you can add another
3
more If statements on it.

If this post helps, Click Yes!

--------------------
(Ms-Exl-Learner)
--------------------



Mark D said:
Morning all

I thought the following would be quite simple but I am having trouble
with
it. Only the first part of the forumla appears to be working (if g4 = all
then the value I want comes up), howerver if I enter any of the others I
get
''FALSE'' appear

Any ideas?

Also if anyone can additionally help me I need to add 1 more IF to this
forumla but it wont let me as I would then have more than 7 functions. So
would anyone have an idea how to reduce this forumla ?

Thanks in advance

=IF(G4="All",COUNT('NA Actuals & Base Info 2009 $'!C3:C36,'EU Actuals &
Base
info 2009 ?'!C9:C48,'EU Actuals & Base info 2009
?'!C59:C73,IF(G4="London",COUNT('EU Actuals & Base info 2009
?'!C22:C48,IF(G4="Stuttgart",COUNT('EU Actuals & Base info 2009
?'!C59:C73,IF(G4="Geneva",COUNT('EU Actuals & Base info 2009
?'!C9:C21))))))))
Click to expand...
Click to expand...
 
You’re Welcome.

--------------------
(Ms-Exl-Learner)
--------------------



Mark D said:
Perfecto

Thanks very much

Ms-Exl-Learner said:
Try this…

=IF(G4="ALL",COUNT('[NA Actuals & Base Info 2009 $.xls]NA Actuals & Base
Info 2009'!$C$3:$C$36)+COUNT('EU Actuals & Base info 2009
€'!C9:C48)+COUNT('EU Actuals & Base info 2009
€'!C59:C73),IF(G4="LONDON",COUNT('EU Actuals & Base info 2009
€'!C22:C48),IF(G4="STUTTGART",COUNT('EU Actuals & Base info 2009
€'!C59:C73),IF(G4="GENEVA",COUNT('EU Actuals & Base info 2009
€'!C9:C21),""))))

Only 4 If statements are there in the above formula so you can add another 3
more If statements on it…

If this post helps, Click Yes!

--------------------
(Ms-Exl-Learner)
--------------------



Mark D said:
Morning all

I thought the following would be quite simple but I am having trouble with
it. Only the first part of the forumla appears to be working (if g4 = all
then the value I want comes up), howerver if I enter any of the others I get
''FALSE'' appear

Any ideas?

Also if anyone can additionally help me I need to add 1 more IF to this
forumla but it wont let me as I would then have more than 7 functions. So
would anyone have an idea how to reduce this forumla ?

Thanks in advance

=IF(G4="All",COUNT('NA Actuals & Base Info 2009 $'!C3:C36,'EU Actuals & Base
info 2009 €'!C9:C48,'EU Actuals & Base info 2009
€'!C59:C73,IF(G4="London",COUNT('EU Actuals & Base info 2009
€'!C22:C48,IF(G4="Stuttgart",COUNT('EU Actuals & Base info 2009
€'!C59:C73,IF(G4="Geneva",COUNT('EU Actuals & Base info 2009 €'!C9:C21))))))))
Click to expand...
Click to expand...
Click to expand...
 
Back
Top