Two of the most frequently used tools in Excel are the
To sum cells based on one criteria use the following
With these also you can add one or multiple criteria to make sure you only select a certain subset of cells in your range that meet your criteria.
You now know how to use some of the most used and most powerful formulas in Excel.
Count
and Sum
functions. These functions let you count and sum cells based on one or multiple different criteria.COUNT in Excel
You can use theCOUNT
function to count the number of cells containing numbers.How to use COUNTIF
If you need to count cells based on a specific criteria then use theCOUNTIF
function. An example would be if you only want Excel to count the cells with values greater than 5.How to use COUNTIFS in Excel
If you need to count cells based on multiple criteria then use theCOUNTIFS
function. An example would be if you want Excel to count all cells with values greater than 5 and that are Red.SUM in Excel
If you want to add multiple numeric values within different cells you can use theSUM
function. In order to do this do it like in the example below:How to use SUMIF
In order to sum cells based on only one criteria then use the followingSUMFIF
function with two arguments.=SUMIF(B1:B5,">5")
To sum cells based on one criteria use the following
SUMIF
function with three arguments, the last argument is to range the sum.
An example would be if you only want Excel to sum the values of cells
that are Blue.=SUMIF(A1:A5,"Green",B1:B5)
How to use SUMIFS in Excel
In order to sum cells based on multiple criteria, use the followingSUMIFS
function. The first argument is the range to the sum.=SUMIFS(C1:C5,A1:A5,"Blue",B1:B5,"Red")
AVERAGEIF and AVERAGEIFS in Excel
Next to the COUNT and the SUM function explained above, you can average numbers in a range of cells with theAVERAGEIF
and AVERAGEIFS
formulas.With these also you can add one or multiple criteria to make sure you only select a certain subset of cells in your range that meet your criteria.
You now know how to use some of the most used and most powerful formulas in Excel.