Function Problems

Multiply Two Cells Based on Text in Another Cell in Excel

Kushani NimanthikaKushani Nimanthika Nov 18, 2026 869 views

Question details

Multiply two cells only when another cell matches or contains specified text, and show a blank result otherwise.

Multiply Two Cells Based on Text in Another Cell in Excel
Product
Microsoft Excel
Device & OS
not provided
Scenario
A worksheet needs to multiply A1 by B1 when C1 has the required text, such as HAT.
Observed behavior
The result cell should show the product when the text condition is met and appear blank for other text.
Before you start

Identify the two cells containing numbers, the cell containing the text to check, and an empty result cell. Decide whether the text must match exactly or can appear within longer text.

Solution 1Recommended

Multiply cells when the text matches exactly

Use this formula when the text cell must equal HAT rather than merely contain it.

1
Select the result cell

Click an empty cell, such as D1, where you want the product to appear.

2
Enter the IF formula

Type =IF(C1="HAT",A1*B1,"") in D1 and press Enter.

3
Apply it to other rows

If needed, drag the fill handle at the bottom-right corner of D1 down to copy the formula to other rows.

Multiply cells when the text matches exactly
Change the result for other text: Replace the final "" with 0 or a message such as "Not applicable" if you prefer a visible result. Excel's standard text comparison is generally not case-sensitive.
WPS Spreadsheet

Apply the same conditional multiplication in WPS Spreadsheet

Use an IF formula in WPS Spreadsheet to multiply two cells when a text value matches your condition and leave the result visually blank otherwise.

  1. 1. Open the worksheet: Open WPS Spreadsheet and use Open to select your workbook.
  2. 2. Select a result cell: Click an empty cell such as D1 beside the values and text you want to check.
  3. 3. Enter the formula: Type =IF(C1="HAT",A1*B1,"") and press Enter. Adjust the cell references and text to match your worksheet.
  4. 4. Fill other rows: Drag the fill handle at the bottom-right corner of the result cell down to apply the formula to additional rows.
Multiply values only when the required text matchesOpen and edit Microsoft Excel XLSX and XLS filesCopy the formula down a column with the fill handleUse familiar spreadsheet formulas and controls
microsoft office alternative - wps office

Frequently Asked Questions

How do I return zero instead of a blank?

Replace the final empty string with 0: =IF(C1="HAT",A1*B1,0). Enter the formula in the result cell.

Will the formula match lowercase hat?

Yes. The comparison C1="HAT" is generally not case-sensitive in Excel, so it also matches hat and Hat.

How can I match HAT inside longer text?

Enter =IF(ISNUMBER(SEARCH("HAT",C1)),A1*B1,"") in the result cell. SEARCH matches the text anywhere in C1, including within a longer word.

Can I use this formula for every row?

Yes. Select the cell containing the formula and drag its fill handle down. Relative references such as A1, B1, and C1 adjust for each row.