SUMIFS

Intermediate

When one condition isn't enough, SUMIFS steps in. This powerful function lets you add numbers based on multiple criteria at once. Perfect for complex business scenarios like "total sales of Electronics in the North region" or "expenses from Marketing paid by Credit Card".

Syntax: =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
Challenge 1
Free
Question:

Calculate the total revenue from Electronics products sold in the North region.

Dataset:
A B C D E F G
1 Order ID Product Category Region Amount Quantity Date
2 ORD-1001 iPhone 15 Pro Electronics North 1299 1 2025-01-05
3 ORD-1002 Nike Air Max Footwear South 150 2 2025-01-06
4 ORD-1003 Samsung TV 55" Electronics East 899 1 2025-01-07
5 ORD-1004 Office Chair Furniture West 299 3 2025-01-08
6 ORD-1005 MacBook Pro Electronics North 2499 1 2025-01-09
7 ORD-1006 Adidas Sneakers Footwear South 120 1 2025-01-10
8 ORD-1007 Standing Desk Furniture East 599 2 2025-01-11
9 ORD-1008 iPad Air Electronics West 699 1 2025-01-12
10 ORD-1009 Puma Running Shoes Footwear North 95 3 2025-01-13
11 ORD-1010 Bookshelf Furniture South 199 1 2025-01-14
12 ORD-1011 AirPods Pro Electronics East 249 2 2025-01-15
13 ORD-1012 Nike Jordans Footwear West 180 1 2025-01-16
14 ORD-1013 Gaming Chair Furniture North 399 1 2025-01-17
15 ORD-1014 Sony Headphones Electronics South 349 1 2025-01-18
16 ORD-1015 Reebok Trainers Footwear East 85 2 2025-01-19
17 ORD-1016 Coffee Table Furniture West 249 1 2025-01-20
18 ORD-1017 Apple Watch Electronics North 449 1 2025-01-21
19 ORD-1018 Converse Shoes Footwear South 65 4 2025-01-22
20 ORD-1019 Desk Lamp Furniture East 79 3 2025-01-23
21 ORD-1020 Dell Laptop Electronics West 1199 1 2025-01-24
22 ORD-1021 Vans Sneakers Footwear North 75 2 2025-01-25
23 ORD-1022 Sofa 3-Seater Furniture South 899 1 2025-01-26
24 ORD-1023 Canon Camera Electronics East 799 1 2025-01-27
25 ORD-1024 New Balance Footwear West 110 1 2025-01-28
26 ORD-1025 Dining Table Furniture North 699 1 2025-01-29
Challenge 2
Free
Question:

What is the total salary for Active employees in the Sales department?

Dataset:
A B C D E F G
1 Employee ID Name Department Position Salary Bonus Status
2 EMP001 John Anderson Sales Sales Manager 75000 5000 Active
3 EMP002 Sarah Mitchell IT Software Engineer 85000 7000 Active
4 EMP003 Michael Chen Marketing Marketing Lead 70000 4500 Active
5 EMP004 Emily Rodriguez Sales Sales Executive 55000 3000 Active
6 EMP005 David Kim IT DevOps Engineer 90000 8000 Active
7 EMP006 Lisa Thompson HR HR Manager 68000 4000 Active
8 EMP007 James Wilson Finance Financial Analyst 72000 5500 Active
9 EMP008 Maria Garcia Sales Sales Executive 52000 2800 Active
10 EMP009 Robert Taylor IT Senior Developer 95000 9000 Active
11 EMP010 Jennifer Lee Marketing Content Manager 62000 3500 Active
12 EMP011 William Brown Sales Sales Executive 54000 3200 Inactive
13 EMP012 Amanda White IT QA Engineer 70000 5000 Active
14 EMP013 Christopher Davis Finance Accountant 65000 4000 Active
15 EMP014 Jessica Martinez HR Recruiter 58000 3000 Active
16 EMP015 Daniel Johnson Marketing SEO Specialist 60000 3500 Active
17 EMP016 Ashley Miller Sales Sales Manager 78000 6000 Active
18 EMP017 Matthew Moore IT System Admin 75000 5500 Active
19 EMP018 Sophia Anderson Finance Finance Manager 82000 7000 Active
20 EMP019 Joshua Thomas Marketing Brand Manager 68000 4500 Active
21 EMP020 Olivia Jackson HR HR Executive 55000 2500 Active
Challenge 3
Premium
Question:

Calculate the total revenue from Main Course items ordered for Delivery.

Dataset:
A B C D E F G
1 Order No Customer Name Item Category Quantity Price Order Type
2 R001 Alex Johnson Margherita Pizza Main Course 2 24 Dine-in
3 R002 Maria Santos Caesar Salad Appetizer 1 12 Takeaway
4 R003 David Lee Grilled Chicken Main Course 1 18 Dine-in
5 R004 Emma Wilson Chocolate Cake Dessert 2 16 Dine-in
6 R005 James Brown Pepperoni Pizza Main Course 1 26 Delivery
7 R006 Sophie Taylor Greek Salad Appetizer 1 11 Takeaway
8 R007 Michael Chen Beef Burger Main Course 3 45 Dine-in
9 R008 Lisa Anderson Tiramisu Dessert 1 9 Dine-in
10 R009 Robert Garcia Pasta Carbonara Main Course 2 32 Delivery
11 R010 Jennifer White Garlic Bread Appetizer 2 12 Dine-in
12 R011 William Davis BBQ Chicken Pizza Main Course 1 28 Takeaway
13 R012 Amanda Martinez Ice Cream Sundae Dessert 3 21 Dine-in
14 R013 Christopher Moore Fish and Chips Main Course 1 22 Dine-in
15 R014 Jessica Thomas Caprese Salad Appetizer 1 13 Takeaway
16 R015 Daniel Jackson Veggie Pizza Main Course 2 44 Delivery
17 R016 Ashley Harris Brownie Dessert 2 14 Dine-in
18 R017 Matthew Martin Chicken Wings Appetizer 1 15 Dine-in
19 R018 Sophia Thompson Lasagna Main Course 1 20 Takeaway
20 R019 Joshua Robinson Cheesecake Dessert 1 10 Dine-in
21 R020 Olivia Clark Hawaiian Pizza Main Course 2 52 Delivery
22 R021 Andrew Lewis Onion Rings Appetizer 1 8 Dine-in
23 R022 Emily Walker Steak Main Course 1 35 Dine-in
24 R023 Ryan Hall Apple Pie Dessert 2 16 Takeaway
25 R024 Madison Allen Mushroom Soup Appetizer 1 9 Dine-in
26 R025 Tyler Young Seafood Pizza Main Course 1 30 Delivery
27 R026 Hannah King Panna Cotta Dessert 1 11 Dine-in
28 R027 Brandon Wright Nachos Appetizer 2 20 Dine-in
29 R028 Megan Lopez Chicken Alfredo Main Course 1 24 Takeaway
30 R029 Justin Hill Lemon Tart Dessert 1 12 Dine-in
31 R030 Lauren Scott Meat Lovers Pizza Main Course 2 60 Delivery
Premium Challenge

Unlock lifetime access to solve all premium challenges

Unlock Now
Challenge 4
Premium
Question:

What is the total amount spent on Software expenses paid by Credit Card?

Dataset:
A B C D E F G
1 Date Category Description Department Amount Payment Method Status
2 2025-01-03 Office Supplies Printer Cartridges IT 245 Credit Card Paid
3 2025-01-05 Travel Client Meeting - Flight Sales 580 Company Card Paid
4 2025-01-07 Software Adobe Creative Suite Marketing 599 Credit Card Paid
5 2025-01-08 Utilities Electricity Bill Admin 1250 Bank Transfer Paid
6 2025-01-10 Marketing Google Ads Campaign Marketing 2500 Credit Card Paid
7 2025-01-12 Office Supplies Stationery Bulk Order Admin 380 Credit Card Paid
8 2025-01-14 Travel Hotel Accommodation Sales 420 Company Card Paid
9 2025-01-15 Software Microsoft 365 Licenses IT 1200 Bank Transfer Paid
10 2025-01-17 Maintenance Office Cleaning Service Admin 650 Bank Transfer Paid
11 2025-01-18 Marketing Social Media Ads Marketing 1800 Credit Card Paid
12 2025-01-20 Travel Taxi Services Sales 125 Cash Paid
13 2025-01-21 Office Supplies Coffee & Snacks Admin 290 Credit Card Paid
14 2025-01-22 Software Slack Premium IT 150 Credit Card Paid
15 2025-01-23 Utilities Internet Service IT 450 Bank Transfer Paid
16 2025-01-24 Marketing Email Marketing Tool Marketing 299 Credit Card Paid
17 2025-01-25 Travel Conference Registration Sales 899 Company Card Pending
18 2025-01-26 Maintenance Equipment Repair IT 380 Credit Card Paid
19 2025-01-27 Office Supplies Desk Accessories Admin 195 Credit Card Paid
20 2025-01-28 Software Zoom Pro License IT 180 Credit Card Paid
21 2025-01-29 Marketing LinkedIn Ads Marketing 1500 Credit Card Paid
22 2025-01-30 Travel Parking Fees Sales 85 Cash Paid
23 2025-01-31 Utilities Water Bill Admin 180 Bank Transfer Paid
Premium Challenge

Unlock lifetime access to solve all premium challenges

Unlock Now
Challenge 5
Premium
Question:

Calculate the total stock of Accessories supplied by "OfficeMax Ltd".

Dataset:
A B C D E F G
1 Product Code Product Name Category Stock Unit Price Supplier Reorder Level
2 PRD-A101 Wireless Mouse Accessories 450 25 TechSupply Co 100
3 PRD-A102 USB-C Cable Accessories 890 12 TechSupply Co 200
4 PRD-A103 Laptop Stand Accessories 230 45 OfficeMax Ltd 50
5 PRD-B201 Office Desk Furniture 85 299 FurniturePro 20
6 PRD-B202 Ergonomic Chair Furniture 120 399 FurniturePro 30
7 PRD-B203 Filing Cabinet Furniture 65 189 OfficeMax Ltd 15
8 PRD-C301 LED Monitor 24" Electronics 180 249 TechSupply Co 40
9 PRD-C302 Mechanical Keyboard Electronics 310 89 TechSupply Co 80
10 PRD-C303 Webcam HD Electronics 95 79 TechSupply Co 25
11 PRD-D401 Printer Paper A4 Stationery 1200 8 PaperWorld Inc 300
12 PRD-D402 Ballpoint Pens Stationery 2500 2 PaperWorld Inc 500
13 PRD-D403 Notebooks Stationery 680 5 PaperWorld Inc 150
14 PRD-E501 Desk Organizer Accessories 340 18 OfficeMax Ltd 75
15 PRD-E502 Monitor Riser Accessories 155 35 OfficeMax Ltd 40
16 PRD-E503 Cable Management Accessories 420 15 TechSupply Co 100
17 PRD-F601 Conference Table Furniture 28 899 FurniturePro 10
18 PRD-F602 Whiteboard Furniture 72 149 OfficeMax Ltd 20
19 PRD-G701 Projector Electronics 45 599 TechSupply Co 15
20 PRD-G702 Speakers Electronics 210 129 TechSupply Co 50
21 PRD-H801 Stapler Stationery 890 12 PaperWorld Inc 200
22 PRD-H802 Paper Clips Stationery 1500 3 PaperWorld Inc 400
23 PRD-I901 Desk Lamp LED Accessories 265 42 OfficeMax Ltd 60
24 PRD-J101 Shredder Electronics 38 189 TechSupply Co 12
25 PRD-K201 Bookends Accessories 180 22 OfficeMax Ltd 45
26 PRD-L301 Calculator Electronics 320 28 TechSupply Co 75
Premium Challenge

Unlock lifetime access to solve all premium challenges

Unlock Now