Explanation You might wonder why we aren’t usingCOUNTIForCOUNTIFS. These functions seem like the obvious solution. This makes it easier to list the days in order with the numbers in column E in sequence. WEEKDAY evaluates each date indates(B5:B15) and returns a number. Without day numbers The day numbers in column E make the formula easier to understand and write. By default, WEEKDAY returns 1 for Sunday and 7 for Saturday, but this is configurable....

The result is an array of TRUE FALSE values, where TRUE represents dates in the month of interest. TheN functionis used tochange these values to ones and zeros, and SUMPRODUCT simply sums and returns the array. The N function can be used to convert TRUE and FALSE to 1 and 0 respectively. When given a text value, the N function returns zero.

Explanation TheYEAR functionextracts the year from a validExcel date. Note: The SUMPRODUCT formula above is an example of usingBoolean logicin anarray operation. This is a powerful and flexible approach to solving many problems in Excel. SUMPRODUCT Function The Excel SUMPRODUCT function multipliesrangesorarraystogether and returns the sum of products.

With default tweaks, 1 = Sunday and 7 = Saturday. So, 2 = Monday, 6 = Friday, and so on. For example, January 1, 2016 is the serial number 42370, and January 8 is 42377. Dates in Excel only look like dates when a date number format is applied. By default, WEEKDAY returns 1 for Sunday and 7 for Saturday, but this is configurable. you’re able to use the WEEKDAY function inside other formulas to check the day of week....

This is a tricky problem in Excel for a couple of reasons. First, there is no direct way to generate a list of sheets in a workbook with a formula. I recommend you place this sheet at the end of the workbook. In the example above, the Errors sheet is the last. Next, we need to create a list of sheet names. Note that the names you enter must match the sheet names exactly....

COUNT takes multipleargumentsin the form ofvalue1,value2,value3, etc. Arguments can be individual hardcoded values, cell references, or ranges up to a total of 255 arguments. COUNTignorestext values, errors, and empty cells. The COUNT function is similar to theCOUNTA function, but COUNTAincludesnumbers and text in the count. Text values and blank cells are ignored. Note thatdatesandtimesare numbers, and therefore included in the count. Numbers include negative numbers, percentages, dates, times, fractions, and formulas that return numbers....

Explanation This formula uses two expressions in a single array inside the SUMPRODUCT function.

But what if we don’t want to (or can’t) add a helper column? In that case, we can use the SUMPRODUCT function withBoolean logic. SUMPRODUCT function TheSUMPRODUCT functionis designed to work with arrays. It multiplies corresponding elements in two or more arrays and sums the resulting products. This allows us to perform a comparison between current and previous sales directly inarray1.

The first two conditions are straightforward. This happens becauseCOUNTIFS is a range-based functionand it won’t accept thearrayreturned by the MONTH function above. The SUMPRODUCT function does not have this limitation and is happy to work with rangesorarrays. SUMPRODUCT function TheSUMPRODUCT functionis programmed to handlearray operationsnatively, without requiring Control + Shift + Enter. Its default behavior is to multiply corresponding elements in one or morearraystogether, then sum the products. When given a single array, it returns the sum of the elements in the array....

All data is in therangeB5:B15. Both approaches are explained below. COUNTIFS function TheCOUNTIFS functionreturns 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 supportslogical operators(>,<,<>,=) andwildcards(*,?) Notice we have single range/criteria pair at this point. To generate a count, all conditions must match. To add more conditions, add more range/criteria pairs. SUMPRODUCT alternative you could also use theSUMPRODUCT functionto count rows that match multiple conditions....

The simplest way to solve this problem is with the COUNTIFS function. COUNTIFS function TheCOUNTIFS functionreturns 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 supportslogical operators(>,<,<>,=) andwildcards(*,?) As the formula is copied down, it returns a count for each value in B5:B16. Note this formula uses thefull column referenceB:B for convenience. With two criteria In the workbook below, we have Color and Price....

In other words, the formula only counts instances of different keywords. We do this by passing the range into SEARCH as the find_text argument. error means no match was found. MATCH supports approximate and exact matching, andwildcards(* ?) SEARCH Function The Excel SEARCH function returns the location of one text string inside another. SEARCH returns the position offind_textinsidewithin_textas a number. SEARCH supports wildcards, and isnotcase-sensitive….

Explanation First, the LEN function counts total characters in the cell B5. LEN returns the result inside of a second LEN, which counts characters without carriage returns. The formula in the example shown will return 1 even if a cell is empty. 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. CHAR can insert characters that are hard to enter into a formula....