Now combine the functions to create a complete business analysis system.
Sample Dataset
|
Order ID |
Employee |
Region |
Product |
Quantity |
Sales |
|
O101 |
Amit |
North |
Laptop |
2 |
120000 |
|
O102 |
Neha |
South |
Mouse |
10 |
5000 |
|
O103 |
Rahul |
North |
Laptop |
3 |
180000 |
|
O104 |
Priya |
South |
Keyboard |
8 |
6400 |
|
O105 |
Amit |
North |
Monitor |
4 |
34000 |
|
O106 |
Neha |
South |
Laptop |
2 |
120000 |
|
O107 |
Rahul |
North |
Mouse |
15 |
7500 |
|
O108 |
Priya |
South |
Laptop |
1 |
60000 |
Analysis
Total North Sales
=SUMIF(C2:C9,”North”,F2:F9)
North Laptop Sales
=SUMIFS(F2:F9,C2:C9,”North”,D2:D9,”Laptop”)
Number of North Orders
=COUNTIF(C2:C9,”North”)
North Laptop Orders
=COUNTIFS(C2:C9,”North”,D2:D9,”Laptop”)
Average North Sales
=AVERAGEIF(C2:C9,”North”,F2:F9)
Highest North Sale
=MAXIFS(F2:F9,C2:C9,”North”)
Lowest North Sale
=MINIFS(F2:F9,C2:C9,”North”)
Total Sales Using Quantity × Price
=SUMPRODUCT(E2:E9,F2:F9)