In this example, the goal is to sum the value of orders that have a status of "Complete" or "Pending". This is a slightly tricky problem in Excel because the SUMIFS function is designed for conditional sums based on multiple criteria, but all criteria must be TRUE in order for a value...Read more
In this example, the goal is to calculate a count for the longest winning streak in a set of data. In the worksheet shown, wins ("W") and losses ("L") are recorded in column C, so this means we want to count the longest consecutive series of W's. Although we are specifically counting the...Read more
The FORMULATEXT function returns a formula as a text string from a cell reference. The FORMULATEXT can be used to extract a formula as text from a cell reference. The text returned by FORMULATEXT is the same as displayed in the ...Read more
In this example, the goal is to use the COUNTIFS function to count data with "OR logic". The challenge is the COUNTIFS function applies AND logic by default.
The COUNTIFS function returns the count of cells...Read more
The TRANSPOSE function is fully automatic and can transpose cells vertical to horizontal, and vice versa. The only requirement is that there be a one to one relationship between source and target cells.
In the example shown, we are transposing a table that is 2 columns by 7 rows (14...Read more
Working from the inside out, the ISNUMBER function will return TRUE when given a number and FALSE if not. When you supply a range to ISNUMBER (i.e. an array), ISNUMBER will return an array of results. In the example, the...Read more
In this example, the goal is to sum the amounts shown in column C by month using the dates in column B. The article below explains two approaches. One approach is based on the SUMIFS function, which can sum numeric values based on multiple...Read more
In this example, the goal is to sum the numbers in column C when the text in column B contains specific pairs of colors. For example, the formula should sum a number when the text contains both "red" and "blue". Order is not important; the two colors can appear anywhere in the cell. However...Read more
