To count unique numeric values in a range with criteria you can use a formula based on the SUM and FREQUENCY functions, together with the IF function to apply criteria.
For example, assume you have a list of employee...

If you want to sum an entire column without supplying an upper or lower bound, you can use the SUM function with and the specific range syntax for entire column.
In the example shown, the formula in G6 is:
=SUM(D:D...

To test values to see if they begin with one of several characters (i.e. begin with x, y, or z) , you can use the COUNTIF function together with the SUM function.
In the example shown, the formula in C5 is:
=SUM(...

To sum the last n columns in a table of data (i.e. last 3 columns, last 4 columns, etc.) you can use a formula based on the INDEX function. In the example shown, the formula in K5:
=SUM(INDEX(data,0,COLUMNS(data)-(K4-...

To sum the same range in one or more sheets, you can use the SUM formula with a special syntax called a "3d reference".
In the example shown, the formula in D6 is:
=SUM(Week1:Week5!D6)
How this formula works
The...

To sum the bottom n values in a range matching criteria, you can use an array formula based on the SMALL function, wrapped inside the SUM function. In the generic form of the formula (above), range1 represents the range...

To calculated a weighted average, you can use the SUMPRODUCT function together with the SUM function. In the example shown, the formula in G7 is:
=SUMPRODUCT(C5:C11,D5:D11)/SUM(D5:D11)
How this formula works
The...

To sum all values in a column or row, you can use the INDEX function to retrieve the values, and the SUM function to return the sum. This technique is useful in situations where the row or column being summed is...

To calculate a running total, you can use the SUM formula with a mixed reference that creates an expanding range. In the example shown, the formula in cell D6 is:
=SUM($C$6:C6)
When this formula is copied down the...

To sum the top values in a range, you can use a formula based on the LARGE function, wrapped inside the SUMPRODUCT function. In the generic form of the formula (above), rng represents a range of cells that contain...

To sum every n rows, you can use a formula based on the OFFSET and SUM functions. In the example show, the formula in F4 is:
=SUM(OFFSET($C$3,(ROW()-4)*5,0,5,1))
How this formula works
In this example, there are 5...

One way to extract data in Excel is to use INDEX and MATCH with a helper column that marks matching data. This avoids the complexity of a more advanced array formula.
In the example shown, the formula in H6 is:
=IF($...

To sum based on multiple criteria using OR logic, you can use the SUMIFS function with an array constant. In the example shown, the formula in H6 is:
=SUM(SUMIFS(E4:E11,D4:D11,{"complete","pending...

To count unique values in a range with a criteria, you can use an array formula based on the FREQUENCY function.
Assume you have a list of employee names together with hours worked on "Project X", and you want know how...

To check to see if a cell contains more than one substring, you can use a formula based on the COUNTIF function.
In the example shown, the formula in C5 is:
=IF(SUM(COUNTIF(B5,{"*abc*","*aaa*...