For reasons explained below, we do this with the LEN function and the SUM function. This formula is dynamic and will recalculate immediately if source data is changed. The approach isexplained in more detail here. For more details, see this training video:How to filter with multiple criteria. COUNTA It is possible to write a simpler formula that relies on theCOUNTA function. However, an important caveat is that COUNTA will return 1 when there are no matching values....

Explanation This formula counts how many values are not in range of a fixed tolerance. SUMPRODUCT then sums the items in the array, and returns a final result, 4. A zero result will be evaluated as FALSE. ABS converts negative numbers to positive numbers, and positive numbers are unaffected.

Explanation There is no direct way to detect a hidden column with a formula in Excel. You might think of using theSUBTOTAL function, but SUBTOTAL only works with vertical ranges. In this example, this range is the named range “key”. In the example shown, columns C and E are hidden. When a column is hidden, the same formula will return zero. By checking if the result is greater than zero, we get a TRUE or FALSE result....

This is a job for theSUBTOTAL function. SUBTOTAL can perform a variety of calculations like COUNT, SUM, MAX, MIN, and more. This makes it ideal for running calculations on the rows thatare visiblein filtered data. Count with SUBTOTAL The first argument,function_num, specifies count as the operation to be performed. Note that SUBTOTALalwaysignores values in cells that are hidden with a filter. Values in rows that have been “filtered out” are never included, regardless offunction_num....

Explanation In this example, the goal is to count visible rows where Region=“West”. Row 13 meets this criteria, but has been hidden. TheSUBTOTAL functioncan easily generatesumsandcountsfor visible rows. However, SUBTOTAL is not able to apply criteria like theCOUNTIFS functionwithout help. The details of this approach are described below. Overview At the core, this formula works by setting up twoarraysinside SUMPRODUCT. The reason the expression is complex is that we need anarrayof results, not a single result....

Explanation The core of this formula is the ROUNDUP function. In this formula, we use that fact to repeat values. The number 2 is simply an offset value, to account for the fact column C is column 3. We subtract 2 to normalize back to 1. Cell B4 holds the value that represents the number of times to “repeat” a count. For number of places, we use zero, so that rounding goes to the next integer....

COUNTA does not count empty cells. The COUNTA function returns the count of values in the list of suppliedarguments. COUNTA takes multipleargumentsin the formvalue1,value2,value3, etc. Arguments can be individual hardcoded values, cell references, or ranges up to a total of 255 arguments. Empty cells are ignored. To ignore empty strings,this example provides a workaround. Numbers include negative numbers, percentages, dates, times, fractions, and formulas that return numbers. COUNTA does not count empty cells....

Cells that contain text, numbers, errors, spaces, etc. Formulas that return an empty string ("")arecounted as blank. COUNTBLANK takes just one argument, range, which must be a cellrange. Note that cell B12 is not included because it contains a space character (" “). Formulas that return empty strings TheIF functionis often used to return empty strings. However it’s worth noting thatCOUNTAandCOUNTIFSwill count B1 asnot emptyin the same case. In other words they will see the empty string ("") returned by IF asnot blank....

This syntax takes a little getting used to. See below for many working examples. When cells meet the criteria, they are added to the count. In the worksheet below, we have a small amount of sales data. The tricky part about using the COUNTIF function is the syntax needed to apply criteria. This is because COUNTIF is in a group ofeight functionsthat split logical criteria into two parts:rangeandcriteria. Because of this design, operators must be enclosed in double quotes ("")....

Explanation In this example, the goal is to count values in three non-contiguous ranges with criteria. To be included in the count, values must be greater than 50. The COUNTIF counts the number of cells in a range that meet the given criteria. However, COUNTIF does not perform counts across different ranges. There are several ways to approach this problem, as explained below. For reasons mysterious, COUNTIF will accept the result from INDIRECT without complaint....

Although common, COUNTIFS has a unique design that splits logical conditions into two parts. The article below has many working examples. The syntax depends on the number of conditions being evaluated. Each condition is provided as a pair ofrange/criteriaarguments: If there are two conditions, there will be tworange/criteriapairs. If there are three conditions, there will be threerange/criteriapairs, and so on. COUNTIFS can handle up to 127 separaterange/criteriapairs. Using COUNTIFS, we can count orders in the data that meet multiple conditions....

The challenge is the COUNTIFS function applies AND logic by default. By default, the COUNTIFS function applies AND logic. Note: this technique will only handle two range/criteria pairs. If you have more than two criteria,consider a SUMPRODUCT formula as described here. This formula “just works” in the current version of Excel, which supportsdynamic array formulas. However, inLegacy Excel, the formula must be entered with Control + Shift + Enter.

This allows the formula to be copied to another column and still work. With INDIRECT and ADDRESS Another approach is to use a formula based on theINDIRECTandADDRESSfunctions. OFFSET is handy in formulas that require a dynamic range. For example, =ADDRESS(1,1) returns $A$1. INDIRECT Function The Excel INDIRECT function returns a valid cell reference from a given text string. INDIRECT is useful when you want to assemble a text value that can be used as a valid reference....