0

# How to use a formula as criteria in sumif?

Updated: 12/17/2022

Wiki User

14y ago

A short explanation of the question: Sometimes it is needed to use a formula as criteria instead of a given criteria. For example if you need to find the sum of numbers that are above or below the average in the range.

For both COUNTIF and SUMIF formulas, the way to use another formula for criteria is the following (pretext: range of numbers is A1:A15):

=countif(A1:A15;">"&average(A1:A15))

or

=sumif(A1:A15;">"&average(A1:A15);A1:A15)

And for the case that any number should be exactly the same as the average, use the "=" sign after the ">" or "<" sign.

Wiki User

14y ago

Earn +20 pts
Q: How to use a formula as criteria in sumif?
Submit
Still have questions?
Related questions

### Which function returns a total for a range with multiple criteria?

The SUMIFS function. It can do multiple criteria, whereas SUMIF does only one.The SUMIFS function. It can do multiple criteria, whereas SUMIF does only one.The SUMIFS function. It can do multiple criteria, whereas SUMIF does only one.The SUMIFS function. It can do multiple criteria, whereas SUMIF does only one.The SUMIFS function. It can do multiple criteria, whereas SUMIF does only one.The SUMIFS function. It can do multiple criteria, whereas SUMIF does only one.The SUMIFS function. It can do multiple criteria, whereas SUMIF does only one.The SUMIFS function. It can do multiple criteria, whereas SUMIF does only one.The SUMIFS function. It can do multiple criteria, whereas SUMIF does only one.The SUMIFS function. It can do multiple criteria, whereas SUMIF does only one.The SUMIFS function. It can do multiple criteria, whereas SUMIF does only one.

### What is the Excel formula for adding how many of a category?

It sounds like you are asking about the SUMIF function. SUMIF adds all numbers in a range of cells, based on a given criteria.=SUMIF(range,criteria,sum_range)range = range of cells that you want to apply the criteria against.criteria = determines which cells to add.sum_range = range of cells to sum.

SUMIF

### What function is useful when you want to add values in a range only if they meet a criteria?

The SUMIF function.

### What function is used to total table values that meet a specified criteria in Excel?

The SUMIF function.

### Is it possible to sum the cells in the given range which meets given criteria using MS-Excel?

Yes. You can use the SUMIF function. Suppose you wanted to add all the cells in the range from B2 to B15 that have values greater than 50: =SUMIF(B2:B15,"&gt;50")

### How can you use the sumif formula to add up the cells in a range in column l if the corresponding cell in J is greater than 19?

To do the entire column, you would do it as follows: =SUMIF(J:J,"&gt;19",I:I) More likely it is just part of the column, say from row 2 to row 20. In that case the formula would be as follows: =SUMIF(J2:J20,"&gt;19",I2:I20) In both cases it is important that the formula is located outside of the ranges that are referred to in the formula.

### Excel formula to add column but omit negative numbers?

You could use the SUMIF function. The range of cells to add are in the first part of the formula and the condition is enclosed in quotation marks. Say your values are in the all cells in column B from B1 to B20. Then in another cell your formula would be: =SUMIF(B1:B20,"&gt;0") This will add all the values from B1 to B20 that are greater than zero.

### How do you find out the summation in multiple Excel sheets to given a certain creteria?

You would use the SUMIF function to do it. Multiple sheets can be referred to by having the name of a sheet, followed by an exclamation mark and the cell reference.You would use the SUMIF function to do it. Multiple sheets can be referred to by having the name of a sheet, followed by an exclamation mark and the cell reference.You would use the SUMIF function to do it. Multiple sheets can be referred to by having the name of a sheet, followed by an exclamation mark and the cell reference.You would use the SUMIF function to do it. Multiple sheets can be referred to by having the name of a sheet, followed by an exclamation mark and the cell reference.You would use the SUMIF function to do it. Multiple sheets can be referred to by having the name of a sheet, followed by an exclamation mark and the cell reference.You would use the SUMIF function to do it. Multiple sheets can be referred to by having the name of a sheet, followed by an exclamation mark and the cell reference.You would use the SUMIF function to do it. Multiple sheets can be referred to by having the name of a sheet, followed by an exclamation mark and the cell reference.You would use the SUMIF function to do it. Multiple sheets can be referred to by having the name of a sheet, followed by an exclamation mark and the cell reference.You would use the SUMIF function to do it. Multiple sheets can be referred to by having the name of a sheet, followed by an exclamation mark and the cell reference.You would use the SUMIF function to do it. Multiple sheets can be referred to by having the name of a sheet, followed by an exclamation mark and the cell reference.You would use the SUMIF function to do it. Multiple sheets can be referred to by having the name of a sheet, followed by an exclamation mark and the cell reference.

### Which Excel function is used to perform conditional calculation?

You can use the IF function to do calcutions based on conditions. You can also use SUMIF, COUNTIF and AVERAGEIF to do calculations too.

### In a spreadsheet with the sales of all salespersons if you want to find the total sales of a salesperson during a specific month what function would you use?

You could use the SUMIF function.