Formula Errors

Excel Formula to Ignore Blanks and Zeros in Row Totals

Muhammad Talha Oct 13, 2026 869 views

Question details

The user needs a method to generate a separate list of row totals from a dataset while automatically excluding any rows that result in zero, blank cells, or empty strings.

How to Calculate Excel Row Totals While Ignoring Blanks and Zeros

Product: Excel for Microsoft 365
Device & OS: not provided
Scenario: Calculating row-by-row totals from a range and filtering out the results dynamically so that meaningless zero values or completely empty rows are not displayed.
Observed behavior: A consolidated list of valid row totals is generated, seamlessly bypassing any source rows that evaluate to zero or contain only blanks.

Before you start

Ensure you are using a version of Excel that supports Dynamic Array formulas, such as Microsoft 365, as the most efficient solution relies on modern functions like LET, BYROW, and FILTER.

Solution 1Recommended

Use the LET and FILTER Functions (Microsoft 365)

This advanced approach uses dynamic array capabilities to sum rows and instantly filter out any zero or blank results in a single formula.

This formula leverages the BYROW function to calculate the sum of each row across a specified range. It assigns these sums to a variable using the LET function, and then applies the FILTER function to extract only the totals that are not equal to zero.

Select the target cell

Click on the cell where you want the new list of filtered row totals to begin (for example, D2).

Enter the dynamic formula

Type the following formula into the formula bar: =LET(lgc,BYROW(A2:B10,LAMBDA(a,SUM(a))),FILTER(lgc,lgc<>0))

Adjust the data range

Modify the range 'A2:B10' within the formula to accurately reflect the actual columns and rows of your source data.

Apply the formula

Press the Enter key. The formula will automatically calculate and spill the non-zero totals down the column.

Use the LET and FILTER Functions (Microsoft 365)

Version Compatibility: The BYROW and LAMBDA functions are exclusive to Microsoft 365 and Excel for the Web. If you are using an older version, this formula will return a #NAME? error.

Solution 2

Use a Helper Column and Standard Filter (Older Versions)

If you do not have Microsoft 365, you can achieve the same result by summing the rows first and then filtering the data manually using standard built-in tools.

Advanced Formula Support

Use WPS Spreadsheet for Advanced Data Filtering

WPS Spreadsheet provides exceptional support for modern array formulas and advanced filtering, allowing you to seamlessly process complex data, sum ranges, and exclude zeros or blanks with ease.

1. Open your dataset: Launch WPS Spreadsheet and open the workbook containing the rows you need to sum.
2. Input the array formula: Select an empty cell and enter your SUM and FILTER combinations, or use standard helper columns for older syntax.
3. Calculate dynamically: Press Enter to instantly process your dataset. WPS will automatically handle the array spillage and exclude the zeros or blank values.

Fully compatible with Microsoft Excel formulas and .xlsx file formatsRobust support for advanced data filtering and criteria-based calculationsLightweight architecture ensures fast processing of large datasets without lag

microsoft office alternative - wps office

Frequently Asked Questions

Why does my LET and BYROW formula return a #NAME? error?

This error occurs if your version of Excel does not support the newly introduced dynamic array functions. You must be using Microsoft 365 or a compatible recent version to utilize functions like LET, BYROW, and LAMBDA.

How can I exclude only blanks but keep zeros in the totals?

To keep valid zeros but exclude purely empty rows, you need to modify the criteria. Since the SUM of empty cells naturally evaluates to 0, you must first check if the source cells are explicitly blank using functions like ISBLANK or COUNTBLANK before performing the calculation.

Can I apply this dynamic array formula to non-adjacent columns?

The BYROW function requires a continuous array or range. If your data is in non-adjacent columns, you will first need to use functions like HSTACK or CHOOSECOLS to combine them into a single, continuous array within your formula.

Excel & Data Problems