Given 1.00 as input, the function returns 1.313035285 as output. Explanation The hyperbolic cotangent is the reciprocal of theTANHfunction. The plot below shows the COTH function’s output in Excel. ACOTH Function The Excel ACOTH function returns the inverse hyperbolic cotangent of a number.

Note: thenamed rangesshown in this example are entirely optional. They are a way to make the formula easier to enter, read, and copy. Count between dates The main function used to solve this problem isCOUNTIFS. To explain how this works, we’ll look first at the total seen in cell H6. The COUNTIFS function belongs to agroup of functionsthat use this syntax. RIGHT returns 20, which is concatenated to the less than or equal to operator (<=)....

So, we use theSUMPRODUCT functionwith custom logic instead. This is a standard number between 1-12, and the details of this formula areexplained here. The results from these two expressions are then compared. As the formula is copied down, it returns the total count of birthdays in each month as listed. See theSUMPRODUCT pagefor more information about howlogical expressionswork inside SUMPRODUCT. Pivot table solution A pivot table is anexcellent solution for this problemas well....

Explanation In this example, the goal is to count birthdays by year. The source data is anExcel Tablenameddatain the range C5:C16. The birthdays we want to count are in the Birthday column. In column E, the years of interest have been previously entered. To perform this conversion, we use adouble negative(–). 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....

The data is in an Excel table calledtable. Bycreating a proper Excel table, we make the formulas easier to read and write. The summary table on the right is constructed by entering Excel times in the Start and End columns. The logical criteria is exactly the same, the only difference is the first argument, calledsum_range. This is the range that contains values to sum, which is the Duration column in the table shown....

This problem can be solved with the COUNTIFS function or the SUMPRODUCT function, as explained below. If you like, you could see the numbers underneath by temporarily formatting the dates with the Generalnumber format. COUNTIFS function TheCOUNTIFS functionis designed to count cells that meet multiple conditions. This syntax is specific toa group of eight functions in Excel. Because the named rangedatecontains 12 dates, each expression inside SUMPRODUCT returns anarraywith 12 TRUE or FALSE values....

Explanation In this example, the goal is to count numbers that fall within specific ranges. For each range, we want toincludeboth the lower value and the upper value. For convenience, the numbers being counted are in thenamed rangedata(C5:C16). This problem can be solved with both the COUNTIFS function and the SUMPRODUCT function, as explained below. The formula in cell G5, copied down, is: COUNTIFS accepts criteria in range/criteria pairs. Note in both cases, we need toconcatenatethe cell reference to thelogical operator....

Explanation In this example, the goal is to count cells equal to a specific value. In this case, we want to count cells that contain “red” in therangeD5:D16. This problem can be solved with the COUNTIF function and the SUMPRODUCT function, as explained below. COUNTIF takes twoarguments:rangeandcriteria: For this problem,rangeis D5:D16 andcriteriais “red”. We place double quotes (“red”) because “red” is atext value. With SUMPRODUCT Another way to solve this problem is with theSUMPRODUCT functionandBoolean logic....

Explanation In this example, the goal is to count codes in a case-sensitive way. The solution is to use theEXACT functionto compare codes and theSUMPRODUCT functionto add up the results. EXACT function The EXACT function’s sole purpose is to compare text in a case-sensitive manner. EXACT takes twoarguments:text1andtext2.Iftext1andtext2match exactly (considering upper and lower case), EXACT returns TRUE. Because we are giving EXACTmultiplevalues in the second argument, it returnsmultipleresults. Each FALSE represents a value in B5:B15 that does not match “ABC”....

The formula in G4 is: TheCOUNTIF functioncounts the number of cells in a range that meet criteria. MATCH supports approximate and exact matching, andwildcards(* ?) Often, MATCH is combined with the…

For convenience, the D5:D15 isnamedcolor. TheCOUNTIFS functionwill allow multiple conditions, but all conditions are joined with AND logic. The article below explains several options. However,criteriais “red” in the first COUNTIF and “blue” in the second. The first COUNTIF returns 4 and the second COUNTIF returns 3, so the final result is 7. This formula works fine, but it is somewhat redundant. These counts are returned directly to theSUM functionin a singlearray:...

Notice thatcriteriais given as a text value in double quotes (""). In this syntax,logical operatorsare joined with numeric values and provided as text. If the value in A1 is changed to a different number, COUNTIF will return a new result. COUNTIF can be used to count cells that…

The classic solution is to use a formula based on the ROWS function and the COLUMNS function. It is also possible to force the COUNTA function to count all cells, empty or not. Both approaches are explained below. Since there are 12 rows in the range B4:H16, ROWS returns 12. This is the total number of cells in an Excel worksheet. TheCOUNTA functionwill count cells that contain numbers, text, or errors....