Level 1WelcomeProject, 3 min

A sales report in four formulas

The task

Here are 40 real-looking orders. In about two minutes you'll turn them into a report: every region, its sales, its order count and the winner. No copying and pasting, no pivot table.

List every region onceIn E2, type =UNIQUE(B2:B41). One formula fills the whole list.
Total sales for each regionIn F2, total Amount for every region in your list at once. In Google Sheets, a formula that works on a whole list goes inside ARRAYFORMULA: =ARRAYFORMULA(SUMIF(B2:B41, E2:E5, C2:C41)).
Count the ordersIn G2, count orders per region the same way: COUNTIF inside ARRAYFORMULA.
Name the winnerIn F7, find the region with the most sales: =XLOOKUP(MAX(F2:F5), F2:F5, E2:E5). Find the biggest number, then return the region next to it.

The data

The starting sheet. Cells outlined in green are where your formulas go.

	A	B	C	E	F	G
1	Order	Region	Amount	Region	Sales	Orders
2	2001	West	555
3	2002	East	140
4	2003	South	210
5	2004	East	345
6	2005	Central	240
7	2006	South	240	Top region
8	2007	Central	135
9	2008	East	330
10	2009	South	65
11	2010	South	190
12	2011	Central	330
13	2012	West	345
14	2013	Central	290
15	2014	South	225
16	2015	Central	365
17	2016	East	185
18	2017	Central	170
19	2018	West	235
20	2019	East	170
21	2020	Central	245
22	2021	South	315
23	2022	West	220
24	2023	South	205
25	2024	East	135
26	2025	Central	75
27	2026	East	190
28	2027	South	330
29	2028	South	100
30	2029	East	170
31	2030	East	220
32	2031	East	260
33	2032	South	120
34	2033	Central	210
35	2034	Central	270
36	2035	South	205
37	2036	South	255
38	2037	Central	190
39	2038	South	125
40	2039	South	155
41	2040	East	210