Dual Conditional Formula

These formula are simpler than the "IF" formula

1. COUNTIF: Syntax =countif(range,criteria)

This formula can be used in case you want to count number of specific items in a range For example you have a list of fruits and you want to count of them how many are apples. This formula makes it simple. The formula is countif(Range,"Apples")

In place of Apples one can also specify a cell values. So if you want to know how many ones are there in the range of numbers this formula is easy. Also you can find the count of a specific value in the list to know whether it is distinct or no in the list

2. SUMIF:

Syntax: =sumif(range,criteria,sumrange)

The sum if formula is very useful if you have to add specific values corresponding a particular range. So you have two lists ---in one column you have list of managers(list1) and another number of sales done(list2). Now you have master list of managers and you want to find from the list how many sales did each manager did. The formula and method is easy. =sumif(list1,"manager1",list2)

Try these formula they are simple to use

CONDITIONAL Formula

Here comes the complex formula

The conditional formula means a formula which can work on conditions specified by the user. In case the condition does not match it will give the result of the second condition

The conditional formula is "IF"

Syntax =if(logical test,[valueif true],[valueiffalse]

Explanation: Logical test is the condition

Value if True means if the condition is satisfied this formula will give the result specified in the formula as true else value if false

Eg: I want to put a formula that if the value of two cells is same then I must get result as "YES" else "NO"...The formula is =IF(C2=B2,"YES","NO")

This If formula is very complex as it can be merged with any excel formula

The three components of this formula can be replaced by any formula

Eg: =IF(COUNT(C2:C10)=4,"YES","NO")

This formula means it first counts the numbers in range C2-C10 and if the result is 4 it gives YES else a NO

Eg: =IF(SUM(C2:C10)=4,"YES","NO")

This formula means it first adds the numbers in range C2-C10 and if the result is 4 it gives YES else a NO

We will learn more on this

COUNT Formula

The count formula is very simple and syntax is =COUNT(C2:C10) where C2 and C10 are the range of the cells . However this formula will only calculate the numbers in the range and will not consider the alphabetic characters

How to write SUM formula

This is one of the most simple excel formula

SUM - used to add numbers

SYNTAX: =SUM(NUMBER1,NUMBER2,.................NUMBERn)

Each number can be specified in cells and in the systax number1 = cell

eg: = sum(A2,B3,D1) will add the numbers in these cells

You can also use combination of numbers annd cells

eg: =SUM(8,C3) will add the number in cell C3 to 8

You can also specify ranges in this formula

eg: =SUM(A6:I6) will add numbers in cells A6,B6,C6,D6,E6,F6,G6 and I6

Now you can use a combination of all above

eg: =SUM(E4:E9,C3,8,H3:I5,2,7)

Here we have added 2 ranges, one cell and 3 numbers.

Such a simple formula with so many combinations possibel makes SUM formula very useful in excel

RECAP FOR BEGINNERS

Thank you everyone for your feedback and ideas. I understand that each the average level of understanding of excel for users is beginners. I am doing a recap of whats done so far and in case have any questions put in your comments or mail me at vmani32@yahoo.co.in

• The best way to learn excel is to open a spreadsheet and do all sorts of experiments
• Opening a spreadsheet is simple Start-->Run-->Excel {Enter}
• Spreadsheet generally opens with 3 sheets (TABS). This can be changed in options
• Depending on the version of excel the number of rows and columns will vary.
• What you see in the sheet are tiny cells which can be formatted to your need and desire
• This gives extensive flexibility to excel and one can present reports in varied formats
• Excel also gives you the flexibility of store huge amount of data. However the file size gets huge and you need a good RAM to work
• You can learn the tricks of formatting from the previous posts by referring to the dummy report at this link https://www.yousendit.com/download/TTZtYnU4NDI5NVZMWEE9PQ
• This report will help a lot
• Excel reportin g is one of the best forms of reporting available which is cheap and does not invlove any major training
• Other systems like SQL, Access, Oracle etc need a lot of technical training. In future I will give you some hints on SQL. May be I can circulate some ebooks
• From the next post I will start sharing one formula every two days
• Please read all the previous posts carefully before going into formulae