A Simple Computer Library

SUMIF

This function in fact resembles the SUM function, that is to add a number within a range of cells.

The word “IF” means this sum was added up with the certain conditions.
The syntax is:
SUMIF(range,criteria,sum_range)
The example below may be easier to see.

..

Combination of Microsoft Excel file and Microsoft Word

First, create data in Microsoft Excel (Sheet1)

Save excel file that you make.
Open Microsoft Word.
Click View, Toolbars, Mail Merge.
Create format letter as you like.
Click Tools, Letters and Mailings, Mail Merge.
Click Letters on radio button.
Click Next:
Starting document , Select recipients,
Click Use an existing list radio button,
Click Browse
Choose excel file in the directory where you save in.
Click Open and choose Sheet1, click OK.
Click Write your letter, More items
Choose one field and click Insert then click Close.
Do the same for the next field.
Click icon View Merged Data to see the result.
If you want to see next record, just click Next Record icon or change the record number.
Note:
If possible Microsoft Excel file and Microsoft Word file in the same directory
Format letter depends on your needs.

AND vs. VLOOKUP

Do we have to use AND function or VLOOKUP function ?
The answer depends on the case that we have.
To see the example, I tried to make an experiment using AND and VLOOKUP funtion to solve simple case.

From this experiment I found the same result, although I used two different formula.
The formula I use to fill up cell C5 by using AND function:
=IF(AND(B5>=85,B5<=100),"A",IF(AND(B5>=70,B5<=84),"B",
IF(AND(B5>=60,B5<=69),"C","D")))
I use Table 1 just to make formula easier to read, so this formula does not have to do with Table 1.
To fill up cell F5, I used Table 2 for VLOOKUP function:
=IF(E5>100,”Too big”,VLOOKUP(E5,$E$14:$F$17,2))
And again which function is easier. The decision is yours.

IF Application

To compare 2 conditions or more and also can be used to conduct conditional tests on values and formulas.
Please see examples below how if function is used.

This example using single if
To fill up cell C3 the formula is:
=IF(B3=’M',’MARRIED’, “NOT MARRIED’)
To fill up cell F3 the formula is:
=IF(F3>=60,’PASS’, “REMEDIAL’)
..
This example using multiple if

To fill up cell C3 the formula is:
=IF(B3=1,1000,IF(B3=2,2000,IF(B3=3,3000,4000)))
..
The next example is the combination of using IF with AND, OR

To fill up cell C3 the formula is:
=IF(AND(C3>=60,D3>10),”$ 10″,”$ 5″)
To fill up cell C3 the formula is:
=IF(OR(B3>=60,C3>10),”Pen”,”Book”)
Note:
AND (two conditions must be true)
OR (one condition true is still acceptable.

HLOOKUP and VLOOKUP

Practice 1

To fill up the Bonus in cell D2 and so on.
If we use Tabel 1, the formula is:
=HLOOKUP(C2,$G$2:$J$3,2)
If we use Tabel 2, the formula is:
=VLOOKUP(C2,$F$7:$G$10,2)

Practice 2

To fill up Name, the formula is:
=VLOOKUP(LEFT(A2,1),$A$8:$D$10,2)
To fill up Salary, the formula is:
=VLOOKUP(LEFT(A2,1),$A$8:$D$10,3)
To fill up Incentive, the formula is:
=VLOOKUP(LEFT(A2,1),$A$8:$D$10,4)
To fill up Bonus, if the two character code multiply by 100 is:
=RIGHT(A2,2)*100

Don’t forget to copy the formula to fill up the next row.
Note : Code have to sorted ascending in Table 1

Practice 3

To fill up JUICE NAME, the formula is:
=HLOOKUP(LEFT(B2,1),$B$13:$D$14,2)
To fill up SIZE, the formula is:
=MID(B2,2,3)
To fill up PACKAGE, the formula is:
=VLOOKUP(RIGHT(B2,1),$A$17:$C$19,2)
To fill up PRICE, the formula is:
=VLOOKUP(RIGHT(B2,1),$A$17:$C$19,3)