Sumif and Countif are excellent ways to summarise data in reports. For this example open my data example. This will open in another window. So in the example there are three tabs, Sheet1 Sheet2 & Sheet3. The data is on Sheet1 and has the following headings ….

SUMIF allows you to add values in a data range based on a criteria. It works like this.

SUMIF(range,criteria,sum_range)

Range is the range of cells that you want evaluated by criteria. In my example I am using the Region column.

Criteria is the criteria in the form of a number, expression, or text that defines which cells will be added. You can use a cell reference that contains your criteria. You can use expressions like 32, “32″, “>32″, or “apples”.

Sum_range are the actual cells to add if their corresponding cells in range match criteria. In my example look at the cells high lighted in red. If sum_range is omitted, the cells in range are both evaluated by criteria and added if they match criteria. This will only work where the range contains numeric data.

COUNTIF allows you to count values or records in a data range based on a criteria. It works like this.

COUNTIF(range,criteria)

Range is the range of cells from which you want to count cells.

Criteria is the criteria in the form of a number, expression, or text that defines which cells will be added. You can use a cell reference that contains your criteria. You can use expressions like 32, “32″, “>32″, or “apples”.

You can get the data example in xls form by clicking here