The first problem is how to determine whole numbers. This can be done with theINT,TRUNC, or MOD functionsas explained in detail here. You might at first be tempted to use theCOUNTIForCOUNTIFSfunctions. Instead, we need a way to work with the array directly withBoolean logic. Next, we need to convert the TRUE and FALSE values to 1s and 0s. The formula in G6: Note the only change to the formula is the logical operator....

Explanation We want to count how often items in columns B, C, and D appear together. For example, how often A appears with C, B appears with F, G appears with D, and so on. This would seem like a perfect use of COUNTIFS, but if we take a stab at add criteria looking for 2 items across 3 columns, it isn’t going to work. A simple workaround is to join all items together in a single cell in ahelper column, then use COUNTIFS with awildcardto count items....

But we need a more advanced formula to count rows that may contain multiple instances of the value. Background study MMULT option One option for solving this problem is theMMULT function. The MMULT function returns the matrix product of two arrays, sometimes called the “dot product”. The result from MMULT is anarraythat contains the same number of rows asarray1and the same number of columns asarray2. The MMULT function takes twoarguments,array1andarray2, both of which are required....

The result from MMULT is anarraythat contains the same number of rows asarray1and the same number of columns asarray2. COLUMN Function The Excel COLUMN function returns the column number for a reference. For example, COLUMN(C5) returns 3, since C is the third column in the spreadsheet. When no reference is provided, COLUMN returns the column number of the cell which contains the formula.

This means we are working with scenario 2 above. With COUNTIFS You might at first reach for theCOUNTIFS function, which handles multiple criteria natively. This works, but you might see this is a somewhat complicated and redundant formula. (If you need a primer on Boolean algebra,this video provides an introduction.) This is the count of rows where group = “a” ANDColor1ORColor2are “red”. To avoid double counting We don’t want to double count rows where bothColor1andColor2are “red”....

An “x” in the Sold column indicates the item has been sold. As is typical in Excel, there are several ways to solve this problem. The article below explains two approaches. COUNTA function TheCOUNTA functioncounts non-blank cells in a range. Note that COUNTA doesn’t care what value is in a cell. In this example, last formula above is an all-in-one formula, to provide more detail. This minimizes the number of calculations performed and reduces errors....

This number is then subtracted from the length of the text with o’s. SUMPRODUCT then sums the numbers in this list and returns a total for all cells in the range. SUBSTITUTE is a case-sensitive function, so it will match case when performing a substitution. Then supply an uppercase character for the text that’s being counted. LEN will also count characters in numbers, but number formatting is not included. SUBSTITUTE Function The Excel SUBSTITUTE function replaces text in a given string by matching....

The typical way to do this is to use a formula based on theSUBSTITUTE functionand theLEN function. LEN Function The LEN function calculates the number of characters in a text string. SUBSTITUTE Function The SUBSTITUTE function performs substitutions in a text string. Notice the LEN function is used twice. The first LEN calculates the length of the original text string. Next, the result from the second LEN is subtracted from the result from the first LEN....

SUBSTITUTE removes the substring from the original text and LEN calculates the length of the text without the substring. This number is then subtracted from the length of the original text. The result is the number of characters that were removed by SUBSTITUTE. Finally, the number of characters removed is divided by the length of the substring. Handling case SUBSTITUTE is a case-sensitive function, so it will match case when running a substitution....

This number is then subtracted from the length of the original text. The result is the number of characters that were removed by SUBSTITUTE. Then, the number of characters removed is divided by the length of the substring. These numbers represent the number of occurrences of the substring in each cell. Ignoring case SUBSTITUTE is a case-sensitive function, so it will match case when running a substitution. LEN will also count characters in numbers, but number formatting is not included....

COLUMNS then returns a final result of 5, since there are 5 columns in the table.

ROWS then returns a final result of 100. Note that the header row is not included in this count. Related videos Introduction to structured references Formulas to query a table

Explanation TheCOUNTIFS functiontakes one or more criteria, entered as range/criteria pairs. The second range/criteria pair is: Matching any time less than the time in E6 (6:30). The TIME function is useful when you want to assemble a proper time inside another formula.