Excel Tips: Functional Logic

Excel Tips

Functional Logic

In the last two blog posts we discussed the logical functions IF, AND and OR.  In this installment, we take the logic to the mathematical functions SUMIF and COUNTIF.  Next month we will continue the discussion with the functions AVERAGEIF and IFERROR, and we’ll touch briefly on the new functions introduced in Excel 2010, SUMIFS, COUNTIFS and AVERAGEIFS.

SUMIF and COUNTIF Functions

The SUMIF function will subtotal cells within a range that meet a criteria (or test).  The syntax for the SUMIF function:

SUMIF(range, criteria, sum_range)

 

range: range of cells to be evaluated by a criteria

 

criteria: criteria in the form of a number, expression, cell reference, text, or function

 

sum_range: range of cells to subtotal by the criteria

 

The COUNTIF function will count all of the occurrences within a range that meet the criteria.  The COUNTIF function syntax:

               COUNTIF(range, criteria)

 

range: range of cells to be counted by a criteria

 

criteria: criteria in the form of a number, expression, cell reference, text, or function

 

A couple of helpful hints are (1) mathematical operators may be included as part of the criteria, but they must be enclosed in double quotes (e.g., “<=50”), and (2) Wildcard characters — question mark (?) and asterisk (*) — also work in the criteria.  Let’s look at an example of SUMIF and COUNTIF.

 

Table1 [SUMIF and COUNTIF functions]

 COUNTIF and SUMIF example

 

The SUMIF formula in cell B9 in Table 1 [=SUMIF($A$2:$A$6,A9,$B$2:$B$6)] says:

IF any of the cells in A2:A6 meet the criteria in cell A9, then sum the corresponding values in range B2:B6.  The $ signs in the formula denotes an absolute reference, which means when the formula is copied to other cells the references made absolute with $’s will not change.

 

The COUNTIF formula in cell B15 in Table 1 [=COUNTIF($A$2:$A$6,A15)] says:

IF any of the cells in A2:A6 meet the criteria in cell A15, then count up those records that meet the criteria.  Using the COUNTIF is very useful for counting how many times each Fund is used or referenced in the source data, found in column A rows 2 through 6.  It will tell you whether a particular fund appears once or several times.

 

In the above example, the criteria cell ranges A9:A12 and A15:A18 are a unique list of Funds taken from the source data range A2:A6.  If duplicates exist in the criterial cell ranges the SUMIF and COUNTIF will double count or triple count or quadruple count, etc., depending on the number of duplicates you have in the criteria cell ranges.  For example, the criteria in cell A10 contains fund 202010D16, which if duplicated in say cell A13 would result in the sum for 202010D16 of 35,897.38 appearing twice.  For this reason, you want only unique values in your criteria cells (A9:A12 and A15:A18, above).  You can use the Data ribbon and the Remove Duplicates command to generate a unique list from the column of source data cells A2:A6 (copied first, of course – don’t destroy your source table data!).

 

You can use the functions SUMIF or COUNTIF to sum or count a range based on criteria or test.  It’s functional logic!  Formula on, dudes…


Comments (0)


Add a Comment





Allowed tags: <b><i><br>Add a new comment:


Latest Posts

 

Latest Comments