The Excel DMAX function gets the maximum value in a given field from a set of records that match criteria. The database argument is a range of cells that includes field headers, field is the name or index of the field to get a max value from, and criteria is a range of...Read more
INDEX and MATCH is the most popular tool in Excel for performing more advanced lookups. This is because INDEX and MATCH are incredibly flexible – you can do horizontal and vertical lookups, 2-way lookups, left lookups, case-sensitive lookups, and even lookups based on multiple criteria...Read more
The Excel DCOUNT function counts matching records in a database using a specified field and criteria. The database argument is a range of cells that includes field headers, field is the name or index of the field to count, and criteria is a range of cells with headers...Read more
In this example, the goal is to calculate the maximum value of every "nth" column in a row of data, where n is a variable entered in the named range M2. This problem can be solved in several ways, as explained below. The explanation below also includes a formula...Read more
Working from the inside out, the logical criteria used in this formula is:
(data)<70
where data is the named range C5:I14. This generates a TRUE / FALSE result for every value in data, and the double...Read more
The SHEETS function returns the total number of sheets in a given reference. SHEETS takes one argument, reference, which should be a cell reference, or a 3D reference. When no references are supplied SHEETS returns the total number of...Read more
The INDIRECT function tries to evaluate text as a valid reference. In this case, the sheet name is pulled from column B and concatenated with an exclamation point and the text A1:
=B5...Read moreThe SHEET function returns the index number of a sheet in Excel. You can use the SHEET function to get a numeric index that represents the order of sheets in an Excel workbook, starting with 1 on the left and ending with N on the right, where N is the total number of sheets in the workbook. The...Read more
The gist of this formula is to replace a given delimiter with a large number of spaces using SUBSTITUTE and REPT, then use the MID function to extract text related to the "nth occurrence" and the TRIM function to get rid of the extra space.
In this snippet, the delimiter (delim...Read more