Calculation of Text and Numbers

[Dnyan]

Hi,

I have a one excel file which have six different sheets and wants total of all sheet in last Total Sheet.
the question is:
1. If the value is all "0" then its wants to show"Nil" value of particular period in Total Sheet.

2. If there is "Not Insured" then in total also wants to show "Not Insured" in Paid Amount column and rest all cells will be blank.

please help.

[darknation144]

For number 1 you would just do something like:

=if((SUM(MD!C5,BI!C8,Mny!C5,Theft!C5,EL!TABLE_29,PL!F5))=0,Nil,SUM(MD!C5,BI!C8,Mny!C5,Theft!C5,EL!TABLE_29,PL!F5))

[Dnyan]

Thank you for help by using this formula the answer is coming #NAME? where I want Nil.

The amount will be "Nil" not a Zero value put in all six sheets.

[darknation144]

Sorry missed the quotes

=if((SUM(MD!C5,BI!C8,Mny!C5,Theft!C5,EL!TABLE_29,PL!F5))=0,"Nil",SUM(MD!C5,BI!C8,Mny!C5,Theft!C5,EL!TA BLE_29,PL!F5))

[Dnyan]

By using this formula some error is coming please check attached file

[Fotis1991]

Hi

In C8 and copy down.

=IF((SUM(MD!C5,BI!C8,Mny!C5,Theft!C5,EL!D4,PL!F5))=0,"Nil",SUM(MD!C5,BI!C8,Mny!C5,Theft!C5,EL!D4,PL!F5))

Change the comma to semi colons, if you have to do this.

Is this, helps ypu?

[darknation144]

Claims.xlsx

There was just an extra (

[Dnyan]

great!!! now one more question as for the year 2010-2011 is not insured so I have type Not Insured in all Sheets and also wants in Total as Not Insured.

[darknation144]

well it is the same principle if(ACell="Not Insured","Not Insured","")

Do all the tabs need to say not insured? can some say not insured and some not?

[Dnyan]

some time there will be changes in year like 2006-2007 or 2009-2010

Should I add this condition in all formulas?

[darknation144]

Use And() to check all the tabs for "Not Insured" this will only return true if they all say "Not Insured". You can then specify what you want it to do if it finds a number.