Statistics › COUNTIFS27 / 144

Progress is saved on this device. Sign in or sign up free to keep it in your account.

MediumFreeCOUNTIFS

COUNTIFS: order status by city

Laras runs an online baby supplies store. She wants a small table with the number of completed and returned orders for each destination city, so she knows which city returns the most.

Task

Fill F2:G4 with the number of orders for the city in column E (E2:E4) and the status in row 1 (F1:G1), using COUNTIFS. Write one formula in F2 that can be copied right and down.

Cells to fillF2:G4

Required functionsCOUNTIFS

Expected result

Loading the expected result…

Check answerCtrlShiftEnter
Fill downCtrlD
Previous / next exerciseAlt←→
Leave the workspaceEscTab

Hint 1
The shape: COUNTIFS(range1, criteria1, range2, criteria2). A row is counted only when every criterion is met.
Hint 2
First pair: the City column (B2:B13) with the city in E2. Second pair: the Status column (C2:C13) with the status in F1.
Hint 3
To copy in both directions: fully lock the data ranges ($B$2:$B$13), lock the city column ($E2) and lock the status row (F$1).

Reveal them one at a time. The last hint nearly gives the answer away.

Give it a try first. The solution unlocks automatically after your first check.

Answer

F2:G4

=COUNTIFS($B$2:$B$13,$E2,$C$2:$C$13,F$1)

Write it in F2, then copy it across F2:G4.

Explanation

=COUNTIFS($B$2:$B$13,$E2,$C$2:$C$13,F$1) counts the orders whose city matches column E and whose status matches row 1. The mixed references $E2 and F$1 let one formula fill the whole table. Bandung has 2 returns out of 4 orders, the most of any city. Orders with the status "Processing" do not fall into either column, so the table total can be smaller than the number of orders.

Starting worksheet: Sheet1
Row	A	B	C	E	F	G
1	Order no.	City	Status	City	Completed	Returned
2	INV-1001	Jakarta	Completed	Jakarta
3	INV-1002	Bandung	Returned	Bandung
4	INV-1003	Surabaya	Completed	Surabaya
5	INV-1004	Jakarta	Completed
6	INV-1005	Jakarta	Returned
7	INV-1006	Bandung	Completed
8	INV-1007	Surabaya	Completed
9	INV-1008	Bandung	Completed
10	INV-1009	Jakarta	Completed
11	INV-1010	Surabaya	Processing
12	INV-1011	Jakarta	Completed
13	INV-1012	Bandung	Returned
14
15
16

Fill in the requested cell, then press Check answer.

Validation result