Posts

where data is an Excel Table in the range (C5:B16). As the formula is copied down, it returns a count of birthdays per year as shown. Video: What is an Excel table. Note: this example has been updated below to show how to create an all-in-one formula with dynamic arrays in the latest version of Excel. SUMPRODUCT function The easiest way to solve this problem is to use the SUMPRODUCT function together with the YEAR function like this in cell F5: Working from the inside out, we use the YEAR function to extract the year from each birthday: Because there are 12 dates in the list, the YEAR function returns 12 values in an array like this: Next, each value in this array is compared against the year in E5, which is 1999....

where “dates” is the named range B5:B104. To get the first day of the month, we use the EOMONTH function like this: EOMONTH returns the last day of the previous month, to which 1 is added to get the first day of the current month. In a similar way, we use EOMONTH to get the first day of the next month: EOMONTH returns the last day of the current month, to which 1 is added to get the first day of the next month....

where data is an Excel Table in the range B5:C16. As the formula is copied down, it returns a new count in each row using the Start and End values in columns E and F to determine a count. COUNTIFS function The COUNTIFS function returns the count of cells that meet one or more criteria, and supports logical operators (>,<,<>,=) and wildcards (*,?) for partial matching. Conditions are supplied to COUNTIFS in the form of range/criteria pairs — each pair contains one range and the associated criteria for that range: Using this pattern we can count ages in brackets like this: We are using the structured reference data[Age] for both ranges, since data is an Excel Table....

where range1 (B5:B16) and range2 (D5:D13) are named ranges. Note: this formula does not care about the location or order of the items in each range. Note: Both formulas below use the SUMPRODUCT function to “tally up” the final count, because SUMPRODUCT handles arrays natively in Legacy Excel. In the current version of Excel, which supports dynamic arrays, you can use the SUM function instead. COUNTIF function The COUNTIF function counts values in a range that meet supplied criteria....

For example, if a user inputs 15%: If a user enters 15.5%, the formula evaluates like this This formula doesn’t validate anything else, for example that percentages are less than 100%. Additional conditions can be added with the AND function. Notes: Data validation rules are triggered when a user adds or changes a cell value. Cell references in data validation formulas are relative to the upper left cell in the range selected when the validation rule is defined, in this case B5....

Examples With a single cell reference, COLUMN returns the associated column number: When a reference is not provided, COLUMN returns the column number of the cell the formula resides in. For example, if the following formula is entered in cell D6, the result is 4: When COLUMN is given a range, it returns the column numbers for that range: In Excel 365, which supports dynamic array formulas, the result is an array {5,6,7} that spills horizontally into three cells, starting with the cell the formula resides in....

While it would have been great had there been only a few possible reasons for malfunctioning formulas. Unfortunately, there are too many things that can go wrong (and often does). But since we live in a world that follows the Pareto principle, if you check for some common issues, it’s likely to solve 80% (or maybe even 90% or 95% of the issues where formulas are not working in Excel)....

Times can be supplied to the HOUR function as text (e.g. “7:45 PM”) or as decimal numbers (e.g. 0.5, which equals 12:00 PM). To create a time value from scratch with separate hour, minute, and second inputs, use the TIME function. The HOUR function will “reset” to 0 every 24 hours (like a clock). To work with hour values larger than 24, use a formula to convert time to decimal hours....

Examples ISREF returns TRUE when value is a reference and FALSE if not: Some functions, like INDEX and OFFSET, can return a reference. As long as the reference is valid, ISREF returns TRUE: To evaluate a reference as text (i.e. “A1”), use the INDIRECT function: Notes Use the INDIRECT function with ISREF to evaluate a reference as text. Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa....

Example 1 | Example 2 | Example 3 | More examples In computer programming, the term LAMBDA refers to an anonymous function or expression. An anonymous function is a function defined without a name. In Excel, the LAMBDA function is first used to create a generic (unnamed) formula. Once a generic version has been created and tested, it is ported to the Name Manager, where it is formally defined and named....

The MAX function takes multiple arguments in the form number1, number2, number3, etc. up to 255 total. Arguments can be a hardcoded constant, a cell reference, or a range, in any combination. MAX ignores empty cells, text values, and the logical values TRUE and FALSE. Basic Example The MAX function returns the largest numeric value in supplied data: When given a range, MAX returns the smallest value in the range:...

ODD takes just one argument, number, which should be a numeric value. With positive numbers, ODD rounds number up to the next odd integer. With negative values, ODD rounds number down away from zero to the next odd negative integer. With one (1) and numbers that are already odd integers, number is unchanged. Examples The ODD function rounds positive numbers up to the next odd integer: Negative numbers are rounded away from zero to the next odd integer: The number 1 and numbers that are already odd integers are unaffected: To round numbers up to the next even integer, see the EVEN function....

Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.

Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.

In VBA, loops allow you to go through a set of objects/values and analyze it one by one. You can also perform specific tasks for each loop. Here is a simple example of using VBA loops in Excel. Suppose you have a dataset and you want to highlight all the cells in even rows. You can use a VBA loop to go through the range and analyze each cell row number....

There are only 3 Excel wildcard characters (asterisk, question mark, and tilde) and a lot can be done using these. In this tutorial, I will show you four examples where these Excel wildcard characters are absolute lifesavers. Excel Wildcard Characters – An Introduction Wildcards are special characters that can take any place of any character (hence the name – wildcard). There are three wildcard characters in Excel: Note: I have not come across many situations where you need to use ~....

The WORKDAY function takes three arguments: start_date, days, and holidays. Start_date must be a valid Excel date. The days argument is the number of days in the future or past to calculate a workday. Use a positive number for days to calculate future dates, and a negative number for past dates. Holidays is an optional argument to specify non-working days. Holidays should be provided as a range that contains valid Excel dates....

You can use the YEAR function to extract a month number from a date into a cell, or to feed a month number into another function like the DATE function: See below for more examples of formulas that use the DATE function. Note: dates are serial numbers in Excel, and begin on January 1, 1900. Dates before 1900 are not supported. To display date values in a human-readable date format, apply the number format of your choice....

where amount is the named range C5:C14, and type is the named range D5:D14, and group is the named range B5:B14. where type is the named range D5:D14, and G4 is a mixed reference with the row locked in order to match the column header in row 4 when the formula is copied down. To sum by group, the range/criteria pair is: where group is the named range B5:B14, and F5 is a mixed reference with the column locked in order to match group names in column F when the formula is copied across....

If you have a huge dataset and the comments are scattered all over the worksheet, it may help to have all comments in a single place as a list. Get a List of All the Comments in a Worksheet In this tutorial, I will show you how to use a VBA code to get a list of all the comments in a Worksheet in a separate worksheet. Something as shown below:...