IFERROR and IFNA in Google Sheets - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants a clean sales report that shows total sales per product. If there is any error in the calculation, it should show 'Check Data' instead of an error message.

📊 Data: You have a table with Product IDs, Product Names, Units Sold, and Unit Price. Some rows have incorrect data that cause errors when calculating total sales.

🎯 Deliverable: Create a new column 'Total Sales' that multiplies Units Sold by Unit Price. Use formulas to replace any errors with the text 'Check Data'.

Sample Data

Product ID	Product Name	Units Sold	Unit Price
101	Notebook	15	2.5
102	Pen	30	1.2
103	Marker		3.0
104	Folder	20
105	Stapler	5	7.5
106	Paper Clips	abc	0.1
107	Glue	10	xyz

1

Step 1: Add a new column header 'Total Sales' in the next empty column (E1).

Expected Result

Column E header is 'Total Sales'.

2

Step 2: In cell E2, enter a formula to multiply Units Sold (C2) by Unit Price (D2). Use IFERROR to show 'Check Data' if there is an error.

=IFERROR(C2*D2, "Check Data")

Expected Result

E2 shows 37.5 (15 * 2.5).

3

Step 3: Copy the formula from E2 down to E8 to apply it to all rows.

Drag or copy formula down from E2 to E8.

Expected Result

Column E shows total sales or 'Check Data' for rows with errors.

4

Step 4: Explain how IFNA can be used instead of IFERROR to catch only #N/A errors, but here IFERROR is better because errors are different types.

Example: =IFNA(C2*D2, "Check Data")

Expected Result

This formula only replaces #N/A errors, other errors show normally.

Final Result

Product ID | Product Name | Units Sold | Unit Price | Total Sales
-------------------------------------------------------------------
101        | Notebook     | 15         | 2.5        | 37.5
102        | Pen          | 30         | 1.2        | 36
103        | Marker       |            | 3.0        | Check Data
104        | Folder       | 20         |            | Check Data
105        | Stapler      | 5          | 7.5        | 37.5
106        | Paper Clips  | abc        | 0.1        | Check Data
107        | Glue         | 10         | xyz        | Check Data

Bonus Challenge

Modify the formula to show 0 instead of 'Check Data' when there is an error in total sales calculation.

Show Hint

Replace the second argument in IFERROR with 0, like =IFERROR(C2*D2, 0).