You will sometimes need to calculate one date based on another date. Most often, you will need the first or last date of the current month, the first or last date of the current year, the previous or future month, year, quarter dates, etc. No matter what kind of dates you need, the simplest way to generate them will include the EOMONTH function. The EOMONTH function returns the last day of the month in relation to the start date. …

# Category: Excel Functions

## SUMIFS by text string length

Values that meet one or more criteria can be added up with the SUMIFS function. The sum of values next to cells containing specific text, i.e., a sum based on text criteria, is possibly the most common use case. The sum of values next to cells containing (non) specific text of a defined length (the defined number of characters in a text string) is a far less common need, but still crucial in specific situations. We can accomplish this …

## UNICODE and UNICHAR functions

The Unicode Standard is the primarily used scheme for internal processing and storage of text, with almost 150 thousand code points representing characters. The Unicode Standard is supported in Excel. However, some functions for text manipulation still support only ASCII or ANSI character sets. Examples of those include the CLEAN function and the TRIM function. The CODE function and the CHAR function used for character manipulation also fall into that category. Full character manipulation is available through the UNICODE function and …

## Combining INDIRECT with ADDRESS

We’ve previously covered how we can use any function to generate a text that could be a valid cell reference, both the column and row parts of the address, and then use the INDIRECT function to convert that text to a cell reference. This process can be greatly enhanced by the ADDRESS function. The ADDRESS function returns a text string that represents the address of a particular cell. Row, column, type of reference (locked or absolute), reference style (A1 …

## INDIRECT function

The INDIRECT function returns the reference specified by a text string. The INDIRECT function syntax is as follows: = INDIRECT ( reference_text ; reference_style ) Reference text can be any text that the function can “convert” to a cell reference. Reference style represents the style of referencing cells in Excel. If omitted or TRUE, the reference text is interpreted as an A1 style reference, i.e., the default style of referencing cells in Excel. If FALSE, the reference text is interpreted …

## How to MATCH numbers formatted as text in Excel

You will sometimes encounter issues while trying to MATCH or LOOKUP data in Excel if your numbers are formatted as numbers in one of your tables and as text in another table. While special formats are available in Excel, they are relatively rarely used and are limited. Some “numbers”, such as identification numbers, are more often stored as text. This is done in order to add leading zeros, hyphens, and other characters to those numbers. However, if we try to …

## RANK functions

Ranking is a data transformation in which values are replaced by their rank when the data is sorted. For any two items, the first is either ranked higher than ranked lower than ranked equal to the second. Values can be ranked from the smallest to the largest, or from the largest to the smallest. Ordinal ranking presumes that all items will receive distinct ordinal numbers, including items that compare equally. Distinct ordinal numbers for items that are equal can …

## SMALL & LARGE functions

The SMALL function returns the k-th smallest number from an array of numbers. The SMALL function syntax is as follows: = SMALL ( array; k ) Array can be any row, column, or a combination of both. k is an integer number representing the position of a number in the array if data in that array would be sorted from the smallest. Consider the following example: If k is 1, the smallest number will be returned. If k is …

## SUMIFS criteria: formulas, functions and conditional statements

We’ve previously established the rules for writing criteria in the SUMIFS function: we can reference any cell we can enter any number directly into the function we can enter text directly in the function, nested inside quotation marks “” a logical test is nested inside of quotation marks “”, with the comparison operator coming first and a number second. We will further expand on the topic in this article. Formulas and functions can also be nested in SUMIFS criteria. If needed, …

## Combining SUMIFS with INDEX MATCH

Using the SUMIFS function, we can sum all of the values in a defined column (or row) that meet one or more criteria. When SUMIFS is combined with INDEX MATCH, that sum range doesn’t have to be defined anymore; it is now rather specified in the function arguments. By combining SUMIFS with INDEX MATCH, we can then sum all of the values that meet multiple criteria in different rows and columns and do this in a simple way, avoiding complex and …