Conditional functions: COUNTUNIQUEIF and AVERAGEIF

14/07/26 •0•0

Let's continue looking at Excel formulas, now let's try to understand COUNTIFUNIQUEIF and AVERAGEIF

作者Leon

翻訳者Editorial

改訂者Editorial

COUNTUNIQUEIFS Function

Counts the number of cells with unique content that match a given criterion entered in the function. This function only exists in Google Sheets.

COUNTUNIQUEIFS Function Syntax

=COUNTUNIQUEIFS(count_range; range1; criterion1; [range2; criterion2;...])

Where the range to count is the range of cells that are the target, and the criterion is a logical test (greater than something or less than something).

Example

Salesperson	Region	Sales
1	South	87925
2	North	129887
3	Southeast	12908
4	South	99087
5	Northeast	234987
6	North	10000
7	South	105000
8	Midwest	1876098
9	Northeast	12387
10	South	12876
11	Southeast	123865
12	Midwest	128543
13	North	987000

1. How many regions of Brazil are in the table?

2. How many regions of Brazil are in the table and are not from the South or Southeast?

Solution

1. =COUNTUNIQUE(B2:B14 )

2. =COUNTUNIQUEIFS(B2:B14; B2:B14;"<>South"; B2:B14;"<>Southeast" )

How to do it in Excel?

In Excel, we can use two functions to try to produce the same effect:

=COUNTA(UNIQUE(B2:B14))

COUNTA will count the number of cells with values, and the UNIQUE function will return an array with all the unique values that exist where you selected. To answer the second question, it becomes even more difficult; we would have to add yet another layer of function such as FILTER. That is why I do not recommend Excel for answering this type of question. For example:

=COUNTA(UNIQUE(FILTER(B2:B14;(B2:B14<>"South")*(B2:B14<>"Southeast"))))

AVERAGEIF or MEDIASE Function

The AVERAGEIF function is the integration of the AVERAGE function with the IF function. Use the AVERAGEIF function to average the values in a range that meet the criteria you specify.

AVERAGEIF Function Syntax

=AVERAGEIF( source_range; criterion; average_range)

Where source_range and average_range are lists of values and criterion is an expression.

Example

Name	Age	Salary
Renato	25	1500
Eduardo	23	1800
Cristina	36	1200
Bárbara	38	1750
Vera	22	1900
Evandro	24	1600
Fátima	31	2100
Carolina	27	1990
Eloisa	30	1300

1. What is the average salary for those over 30 years old?

2. What is the average salary for those under 30 years old?

Solution

1. =AVERAGEIF( B4:B10;">30"; C2:C10) = R$ 1,683.33

2. =AVERAGEIF( B4:B10;"<30"; C2:C10) = R$ 1,681.67

AVERAGEIFS or MEDIASES Function

The AVERAGEIFS function, one of the math and trigonometry functions, adds all its arguments that meet multiple criteria.

AVERAGEIFS Function Syntax

=AVERAGEIFS( average_range; criteria_range1; criterion1; [criteria_range2; criterion2]; ... )

Example

Type	Price	City	Bedrooms	Garage
Cozy Apartment	230000	Areeiro	3	No
Holiday Bungalow	197000	Bela Vista	2	Yes
Villa	345678	Bela Vista	4	Yes
Duplex	321900	Areeiro	2	Yes
Mansion	450000	Bela Vista	5	Yes
Colonial Mansion	395000	Bela vista	4	No

1. What is the average price of a house in Belavista with three or more bedrooms and a garage?

2. What is the average price of a house in Areeiro with three or fewer bedrooms and no garage?

Solution

=AVERAGEIFS(B2:B7 ; C2:C7 ; "=Belavista" ; D2:D7 ; ">2"; E2:E7 ; "=Yes") = R$ 397,839.00

=AVERAGEIFS(B2:B7 ; C2:C7 ; "=Areeiro" ; D2:D7 ; "<4"; E2:E7 ; "=No") = R$ 230,000.00

Exercises

Access here the spreadsheet that will be used for the next exercises

1. What is the average of the grades above 6?

2. How many different grades can be seen?

3. What is the average grade for students who missed more than 2 times? And how many different grades can be seen for students who missed more than 2 times?

4. And the average grade for students who did not miss the first two classes? And how many different grades can be seen for students who did not miss the first two classes?

Use the following table for the next exercises:

Order	Customer	Dish	Amount (R$)	Payment Method	City
101	João	Pizza	50.00	Card	São Paulo
102	Maria	Hamburger	30.00	Cash	Rio de Janeiro
103	Carlos	Sushi	80.00	Card	Belo Horizonte
104	Ana	Pizza	55.00	Pix	Curitiba
105	Roberto	Hamburger	32.00	Cash	São Paulo
106	Fernanda	Sushi	85.00	Card	São Paulo
107	Ricardo	Pizza	52.00	Pix	Belo Horizonte
108	Camila	Hamburger	35.00	Card	Curitiba
109	Luiz	Sushi	90.00	Cash	Rio de Janeiro

5. How many different cities have registered orders?

6. How many customers paid with Pix or Cash?

7. What is the average amount of orders paid by Card?

8. What is the average amount of Pizza or Sushi orders made in São Paulo?

Use the following table for the next exercises:

Code	Product	Category	Stock	Price (R$)	Supplier
P001	Notebook	Electronics	12	3500.00	Tech Store
P002	Smartphone	Electronics	20	2500.00	DigitalShop
P003	Printer	Office	8	800.00	Office Pro
P004	Desk	Furniture	5	1200.00	MoveisPlus
P005	Chair	Furniture	10	600.00	MoveisPlus
P006	TV 55"	Electronics	15	2800.00	DigitalShop
P007	Keyboard	Accessories	30	200.00	Tech Store
P008	Mouse	Accessories	25	150.00	Tech Store
P009	Monitor	Office	7	900.00	Office Pro