The result is 3, since there are three rows with a color of “blue” and quantity greater than 15. COUNTIFS function The COUNTIFS function returns the count of cells that meet one or more criteria. COUNTIFS can be used with criteria based on dates, numbers, text, and other conditions. COUNTIFS supports logical operators (>,<,<>,=) and wildcards (*,?) for partial matching. In this example, we want to count orders where the color is “blue” in column B and the quantity is greater than 15 in column C....

where variance is the named range D5:D15. In other words, the result is the sum of the values in D5:D15 converted to absolute values. See below for details about the other formulas that appear in this example. Sum absolute variances The formula in F6 calculates the sum of absolute variances with the ABS function together with the SUMPRODUCT function: In this formula, ABS returns the absolute value of variances to SUMPRODUCT in a single array: SUMPRODUCT then returns the sum, 975....

This is a much simpler formula, but you can’t copy it down column H, because the column reference won’t change. The example on this page therefore is meant to show one way to set up a formula that references a table with a variable column reference. Working from the inside out, the MATCH function is used to find the position of the column name listed in column G: MATCH uses the value in G5 as lookup value, the headers in Table1 for array, and 0 for match type to force an exact match....

In this blog post, I will show you a super way to create a dynamic target line in an Excel chart, that can help you track your performance over the months. Something as shown below: The target line is controlled by the scroll bar, and as if the target is met (or exceeded) in any of the months, it gets highlighted in green. Creating a Dynamic Target Line in Excel Bar Chart There are 3 parts to this chart: The Bar Chart I have data as shown below:...

As a result, you can use simple addition and subtraction to calculate dates. In this case, we have a date in the future, so need to subtract the current date to get “days remaining”. The TODAY function returns the current date automatically, so we only need to subtract the result of TODAY() from the date values in column B. Note: if a date has passed, the result will be negative....

Where data is an Excel Table in B5:B16. With five values in E5:E9, COUNTIF returns the five counts to cell F5, and the results spill into the range F5:F9. See below for progressively more advanced options including all-in-one formulas. Manual formula Note that it is possible to build a summary table with formulas manually. The basic approach is to copy all values, use the Remove Duplicates command to get unique values, and then copy a COUNTIF formula into each cell that needs to show a count: Video: How to build a simple summary table This works fine, but the summary table will not update automatically if new values are added or removed from the data....

Array formulas include one or more array operations. The final result of an array formula can be a single value or multiple values. Array operations and Excel 365 The distinctive feature of an array formula is that it includes an array operation, and there is a big difference in how array operations are handled in the current version of Excel versus older versions. In Excel 2019 and older, many formulas with array operations had to be entered with control + shift + enter to work properly....

Binary data occurs when an observation can be placed into only two categories. For example, when tossing a coin, the result can only be heads or tails. Or, when rolling a die, the result can either be 6 or not 6. Note: the BINOMDIST function is classified as a compatibility function, replaced by the BINOM.DIST function. Example In the example shown, the BINOMDIST function is used to calculate the probability of rolling a 6 with a die....

How It Works The function performs a logical AND operation on each corresponding bit in the binary representation of the two numbers. 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.

The Excel COUPPCD function returns the previous coupon date before the settlement date. The settlement date is the date the investor takes possession of a security. The maturity date is the date when the investment ends and the principle plus accrued interest is returned to the investor. The frequency is the number of interest payments per year. Basis specifies the method used to count days (see below). In the example shown, the formula in F6 is:...

When to use Excel HLOOKUP Function Excel HLOOKUP function is best suited for situations when you are looking for a matching data point in a row, and when the matching data point is found, you go down that column and fetch a value from a cell which is specified number of rows below the top row. What it Returns It returns the specified matching value. Syntax =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])...

Examples If A1 contains the #N/A error, ISNA returns TRUE: ISNA returns FALSE for other values and errors: You can use the ISNA function with the IF function test for #N/A and display a friendly message if the error occurs. For example, to display a message if A1 contains #N/A and the value of A1 if not: The IFNA function is a more efficient way to trap the #N/A error. See VLOOKUP without NA error for an example....

The RANDARRAY function takes five arguments, none of which are required: rows, columns, min, max, and integer. By default, rows, columns, and max default to 1, while min defaults to zero and integer defaults to FALSE. Without any arguments, RANDARRAY will return a decimal value between 0 and 1: Use rows and columns to control the number of values returned: Use min and max to set a lower and upper threshold for values....

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.

Note: In Mac 2016, the Pivot Table Wizard appears to be gone. If you find a workaround, let us know. 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.

A few ways of running a macro includes using the macro dialog box, assigning the macro to a button, using a shortcut, etc. Apart from these user-initiated macro executions, you can also use VBA events to run the macro. Excel VBA Events – Introduction Let me first explain what is an event in VBA. An event is an action that can trigger the execution of the specified macro. For example, when you open a new workbook, it’s an event....

The concept of exclusive OR is more common in the world of programming. In plain English, you can think of a sentence like this: “I’m either going to visit New York or San Francisco this summer”. Nothing prevents the speaker from visiting both, but the meaning is clearly that they plan to visit only one or the other. If they visit one or the other, the original statement is TRUE....

If you have dates that include time, you can use the INT function to extract just the date part. The INT function returns the integer portion of a number that includes a decimal value. So, assuming A1 contains the date and time, June 1, 2000 12:00 PM (equivalent to the number 36678.5), the formula below returns just the date portion (36678): The time portion of the value (the fractional part) is discarded....