To find sum of numbers based on criteria, we can use a combination of **SUM, MATCH, COUNTIF & OFFSET** functions to retrieve the output.

**SUM: **Adds all the numbers in a range of cells

**Syntax: =SUM(number1,number2,…)**

There can be maximum 255 arguments. Refer below shown screenshot:

**MATCH** function searches for a specified item in a selected range of cells, and then returns the relative position of that item in the range.

**Syntax =MATCH(lookup_value,lookup_array,match_type)**

**COUNTIF: **Counts the number of cells within a range that meets the condition.

**Syntax: =COUNTIF(range,criteria)**

**OFFSET: **Returns a reference to a range that is a given number of rows and columns from a given reference.

**Syntax: =OFFSET(reference,rows,cols,height,width)**

**reference: **It is a reference to a cell or range of cells from which the offset will be applied.

**rows: **The number of rows up or down that you want to apply as the offset to the range.

**cols: **The number of columns left or right that you want to apply as the offset to the range.

**height: **This is optional. It is the number of rows that you want the returned reference to be.

**width: **This is optional. It is the number of columns that you want the returned reference to be.

**Let us take an example:**

Column A contains an ascending list of numbers. We need a formula that will sum up all the numbers in column A larger than 2 which is our criterion in cell C2.

- The formula in cell D2 would be
**=SUM(OFFSET(A2,MATCH(C2,A2:A10,0),0,COUNTIF(A2:A10,”>”&C2)))**

- The formula has taken the sum of numbers which are greater than 2.
- If we change the criteria in cell C2 from 2 to 5 then the result would be updated automatically. This time formula will calculate sum of the numbers which are greater than 5.