The Problem
In this guide, we will explore exactly how to calculate discounts based on category in Excel. This kind of setup is common when different departments follow different discount rules.
The tricky part is consistency. A furniture item may need a much larger discount than an electronics item, so one flat formula is not enough. If the rate is applied manually, it is easy to use the wrong percentage on the wrong row.
In this challenge, each product already has a category and a base price. Your job is to calculate the correct discount amount for each row, then summarize how many products were processed and the total discount value across the whole list.
How We Solve It
The logic starts with category matching. Excel checks the category in column B, chooses the correct percentage, and multiplies it by the base price in column C.
The challenge solution uses IFS, but this is also a good example of how the same pricing logic can be expressed with SWITCH or a lookup table when the workbook gets larger.
Method 1: IFS by Category
Method 1: Use IFS to apply a different rate based on the category in the row.
IFS works well when you have a short set of categories and each one maps to a different formula. Excel checks the department name and uses the corresponding percentage for that row.
This is the most direct fit for the challenge because the rates are simple and the list of categories is small.
=IFS(B2="Electronics",C2*0.05,B2="Furniture",C2*0.2,B2="Clothing",C2*0.15)
Method 2: SWITCH to Return the Rate
Method 2: Use SWITCH to convert the category into a discount rate first.
SWITCH is helpful when you want the category-to-rate mapping to be easy to read. Instead of writing the multiplication directly inside each condition, you can return the rate and multiply once.
That makes the formula feel cleaner when the same price calculation happens after the category is identified.
=C2*SWITCH(B2,"Electronics",0.05,"Furniture",0.2,"Clothing",0.15)
Method 3: Use a Separate Rates Table
Method 3: Use a lookup table when discount rates need to be maintained separately.
If the rates change often, a separate table is easier to maintain. A lookup formula can fetch the correct rate from that table and keep the main pricing area cleaner.
That approach is not required here, but it is useful when category rules start growing beyond a few hard-coded options.
=C2*VLOOKUP(B2,$F$2:$G$4,2,FALSE)
Function Explanation
1. IFS
IFS checks multiple conditions in order and returns the first matching result. In this challenge, that means applying the correct discount rule based on the category in the row.
It is useful when category rules are few, clear, and handled directly in one formula.
Learn more this functionIFS
2. SWITCH
SWITCH maps one value to a matching result. Here, that means turning a category like Furniture into a discount rate like 0.2.
This can make category formulas easier to scan when the matching part is more important than the calculation part.
Learn more this functionSWITCH
3. SUM
SUM adds the calculated discount amounts together. In the summary area, it tells you the total discount value being given across all rows.
That turns the row-level calculations into a single total the business can review quickly.
Learn more this functionSUM
The most common mistake here is using the wrong decimal rate. A 15 percent discount should be written as 0.15, not 15.
Calculate the discount amount for each product using the department category in column B and the base price in column C. Once every row is done, finish the summary section so the worksheet shows both the total number of records and the total discount value being offered.