Functions used in this book 6 7 8 9 10 pg 223 224 225 226

6. =ISNUMBER

This function is usually used to check whether the content of a cell is a number. The full syntax of this function is:

=ISNUMBER(Cell Address)

Example:

We want to check in cell G5 whether the content of cell A5 is a number or not a number. At cell G5, We would write:

=ISNUMBER(A5)

If G5 contains a number, we would see TRUE in cell G5, otherwise FALSE.

7. =SUMIF

This function is used in a cell to sum all numbers in certain area in data area if another area in the same data area contain a particular item. The full syntax of this function is:

=SUMIF(Item to sum, Area where item exists, Area which contain figures to sum)

Example:

In cell A5, we want to sum each amount in range C5:C30000 for each cell in range D5:D30000 which is the same with the content of cell B5. We would then write the following in cell A5:

=SUMIF(B5, D5:D30000,C5:C30000)

8. =COUNTIF

We can use this function in a cell to count how many times an item exists in a data area. The full syntax of this function is:

=COUNTIF(Data area address, Item)

Example:

We want to count in cell A5, how many times the item LAND is duplicated in the range B4:B2000. In cell A5, we would write:

=COUNTIF(B4:B2000, "LAND")

If LAND occurs 5 times, we will see 5 in cell A5.

224

9. =MATCH

The full syntax of the function is:

=MATCH(Item being searched, Column Range to search, 0)

The parameter 0 must always be stated. This function will return the relative row position (not the actual sheet row position) of the first item in the searched column range that match the item being searched.

Example:

In cell A1, we want to find the first occurrence of an account name 'LAND' in range D5:D100. So we write in cell A1 this formula:

= MATCH("LAND",D5:D100)

The function will search for "LAND" in range D1:D100 from top to bottom. If it finds one, it will return the relative row position of the item in the range. (If the position it finds is the 8th, the actual sheet row is 17). If it can't find, it will return #NA.

10. =SUMPRODUCT

This function is used to get the sum of a certain item satisfying more than one criteria. For example, we want to sum the amounts in range G4:G2000 where the account name in range B4:B2000 is 'Stationery' and the month in range D4:D2000 is '4'. We will write the formula like this:

=SUMPRODUCT((B4:B2000="Stationery")*(D4:D2000="4")*(G4:G2000))

In this example, we have 2 criteria. If you have more than 2 criteria, just add the criteria in brackets after the asterisk symbol.

You can arrange the criteria in any order. For example, the above can be rearranged to:

=SUMPRODUCT((D4:D2000="4")*(G4:G2000)*(B4:B2000="Stationery"))

225

The asterisk (*) sign is a multiplication sign in Excel. The ranges containing the criteria and the amounts to be added up must have the same dimension. The range containing the amounts to sum must all contain numbers, otherwise the function will return an error #VALUE.

In the above example, Excel will search row by row from row 4 to 2000. For example, at row 4, if it find that the criterion B4="Stationery" is met, it will return 1. Next, if it find that the criterion D4="4" is met, it will return 1. Let's say that the amount in cell G4 is 1000, so the effect is 1*1*100 which is 100. If any one of the criteria is not met, it will return 0. Let's say the first criteria is not met, the effect is 0*1*100 which results in 0. Excel will make this computation row by row, and will sum all the results.

226