Function Problems

Sum Two Columns When a Cell Contains N in Excel

Algirdas JasaitisAlgirdas Jasaitis Nov 18, 2026 868 views

Question details

Add values from two columns only for rows where a criteria column contains the letter N.

Sum Two Columns When Another Column Contains N in Excel
Product
Microsoft Excel
Device & OS
not provided
Scenario
A worksheet has criteria in column B and values to add in columns C and D.
Observed behavior
The desired total includes values from columns C and D only when the corresponding cell in column B contains N.
Before you start

Identify the criteria and value ranges, and make sure they cover the same rows. The examples use B3:B5 for the criteria and C3:D5 for the values.

Solution 1Recommended

Use SUMIFS to Add Both Columns for Matching Rows

Use this compatible formula when the criteria cell can contain N anywhere in its text.

1
Select a result cell

Click an empty cell where you want to display the total.

2
Enter the formula

Type =SUMIFS(C3:C5,B3:B5,"*N*")+SUMIFS(D3:D5,B3:B5,"*N*") and press Enter.

3
Adjust the ranges

In the formula bar, replace B3:B5, C3:C5, and D3:D5 with your actual ranges, keeping their row boundaries aligned.

Use SUMIFS to Add Both Columns for Matching Rows
Contains versus exact match: The wildcard criterion "*N*" matches text containing N, regardless of case. Use "N" instead if the criteria cell must equal N exactly.
Calculate in WPS Spreadsheets

Sum Matching Rows in WPS Spreadsheets

Use SUMIFS in WPS Spreadsheets to add two value columns when a criteria cell contains N.

  1. 1. Open your spreadsheet: In WPS Spreadsheets, use Open to select your workbook.
  2. 2. Choose a result cell: Click an empty cell where the total should appear.
  3. 3. Enter the formula: Type =SUMIFS(C3:C5,B3:B5,"*N*")+SUMIFS(D3:D5,B3:B5,"*N*") and press Enter.
  4. 4. Match your worksheet ranges: Edit the ranges in the formula bar so the criteria and value columns cover the same rows.
Sum multiple columns using a contains criterionOpen and save Microsoft Excel XLSX filesEnter familiar spreadsheet formulas in the formula bar
microsoft office alternative - wps office

Frequently Asked Questions

How do I sum rows where the cell is exactly N?

Replace "*N*" with "N" in each SUMIFS criterion: =SUMIFS(C3:C5,B3:B5,"N")+SUMIFS(D3:D5,B3:B5,"N").

Does the contains formula match lowercase n?

Yes. Both SUMIFS with "*N*" and SEARCH("N",B3:B5) match uppercase and lowercase letters.

What happens if no criteria cells contain N?

The SUMIFS formula returns zero. The FILTER example also returns zero because its third argument specifies 0 for no matches.

Can I sum columns that are not next to each other?

Yes. Use a separate SUMIFS for each value range, such as =SUMIFS(C3:C5,B3:B5,"*N*")+SUMIFS(E3:E5,B3:B5,"*N*"). Keep all ranges aligned to the same rows.