COUNTIFS: Counting the number of items in a collection SUMIFS: Summing up the values of a set of items AVERAGEIFS: Average value of a set of items
In the first two months of our business, we have made a total of $2,811.06 in sales. This is an increase of $1,521.06 from the same period last year.
Excel COUNT, SUM and AVERAGE
COUNT(*) This will give us the number of mobile phones that we have sold in a given period of time.
SUM(Sales)
AVERAGE(SELECT COUNT(*) FROM PHONE_SALE)
The study found that the average person can expect to live for around eighty years. ..
The Count, Sum and Average formulas will only work for records where the cell value is in number format. Any record within the formula range (i.e. E2:E16 in this example) not in the number format will be ignored.
Please ensure that all cells within the COUNT, SUM and AVERAGE formula are all formatted as Number, not Text. Try to use the same formula, but with E:E as the range instead of E2:E16. It will return the same result as before because it ignores the header (i.e. Sale Price), which is in text format.
COUNTIFS(Sales) This will count the number of sales for a given period of time. SUMIFS(Sales) This will sum up all the sales for a given period of time. AVERAGEIFS(Sales) This will average out all the sales for a given period of time.
COUNTIFS
The formula returns 6 which is the number of sales for products shipped from the US warehouse.
SUMIFS
The formula shows that $6,050 was made in sales from products shipped from the USA warehouse. ..
AVERAGEIFS
The phone formula shows that we sold the product for around $1,008 per phone in the United States.
We need to add the data range to be checked followed by its criteria to find out the same figures for products sold in the USA.
Please see example below where a second criterion is added to the initial criteria checks. (Blue text indicates the first criteria and red indicates the second criterion) Please see example below where a second criterion is added to the initial criteria checks. (Blue text indicates the first criteria and red indicates the second criterion) Please see example below where a second criterion is added to the initial criteria checks. (Blue text indicates the first criteria and red indicates the second criterion)
You will notice that Excel also has COUNTIF, SUMIF and AVERAGEIF formulas without the suffix “S”. Those are used similar to COUNTIFS, SUMIFS and AVERAGEIFS. However, those without the suffix “S” in the formula have the limitation of only allowing one criteria per formula.
The syntax for these functions is slightly different, so I recommend using COUNTIFS, SUMIFS and AVERAGEIFS only if you need them for one criterion or more. ..
