Function Problems

How to Simplify Repeated SUMIFS Criteria Using LAMBDA Function in Excel

Huda Qurayshi Sep 28, 2026 869 views

Question details

The user wants to simplify multiple repeated SUMIFS criteria in an Excel table by using the LAMBDA function.

How to Simplify Repeated SUMIFS Criteria Using LAMBDA in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Calculating totals across a structured dataset with multiple categories (dates, types, classes, amounts) where using traditional SUMIFS results in long, repetitive, and hard-to-maintain formula blocks.
Observed behavior: The current approach requires numerous separate SUMIFS formulas (e.g., ten formulas for ten different classes). The user needs a streamlined custom LAMBDA function to reduce complexity and make the spreadsheet easier to maintain.

Before you start

Ensure you are using an updated version of Microsoft 365, as the LAMBDA function is not available in older standalone versions of Excel (like 2016 or 2019). Also, map out a complete sample of your table structure (dates, types, classes, and amount fields) beforehand so you can accurately identify which variables will change in your formula.

Solution 1Recommended

Define a Custom LAMBDA Function in the Name Manager

Create a reusable custom function using LAMBDA to handle repeated SUMIFS criteria without retyping the entire formula across your spreadsheet.

A LAMBDA function allows you to define custom, reusable formulas without using VBA. By identifying the changing variables in your SUMIFS (such as the specific class or type), you can consolidate a complex multi-part formula into a single, clean function call.

To make this reliable, your data structure must be consistent. The formula design heavily depends on whether each class uses a different amount column and how many criteria combinations are required.

Identify fixed ranges and variables

Review your existing SUMIFS formula. Determine which ranges remain constant (like the date range or total amount column) and which criteria change (like the specific 'class' or 'type').

Open the Name Manager

Navigate to the Formulas tab on the Excel ribbon and click on Name Manager.

Create a new defined name

Click the New button. In the Name field, enter a descriptive custom name for your function, such as 'CustomSumifs'.

Input the LAMBDA formula

In the 'Refers to' box, define your parameters and the SUMIFS logic. For example: =LAMBDA(target_class, target_type, SUMIFS(AmountRange, ClassRange, target_class, TypeRange, target_type)). Click OK to save.

Apply your new custom function

Return to your worksheet, select the cell where you want the total, and type =CustomSumifs("Class A", "Type 1"). Press Enter to see the summarized result.

Define a Custom LAMBDA Function in the Name Manager

Testing your LAMBDA formula: Before adding your formula to the Name Manager, test it directly in a standard cell by appending the parameter values in parentheses at the end, like this: =LAMBDA(x, y, SUMIFS(...))(value_x, value_y). This helps you troubleshoot errors easily.

Solution 2

Use the LET Function for Single-Cell Simplification

Use the LET function to define variables within a complex SUMIFS formula if you do not need cross-workbook reusability via Name Manager.

Free Microsoft Office alternative

Manage Complex Data and Formulas with WPS Office

If you are struggling with complex formula management or find that advanced functions are missing from your current software version, consider switching to WPS Office. It provides powerful data analysis tools, including Pivot Tables to summarize data effortlessly, completely free of charge.

1. Download the software: Visit the official WPS Office website and download the free installation package for your operating system.
2. Install WPS Office: Run the installer and follow the on-screen instructions to set up the suite on your device.
3. Open your Excel workbook: Launch WPS Spreadsheet and seamlessly open your existing .xlsx files to manage your complex datasets using built-in Pivot Tables and standard formulas.

Fully compatible with Microsoft Excel (.xlsx) formats and standard SUMIFS formulas.Lightweight installation and incredibly fast processing for large datasets.Built-in advanced features like Pivot Tables to summarize complex classes and types without manual formulas.Familiar interface that ensures a seamless migration with zero learning curve.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my LAMBDA function returning a #NAME? error?

A #NAME? error usually indicates that your version of Excel does not support the LAMBDA function (it is strictly available for Microsoft 365 subscribers) or that there is a spelling mistake in the Name Manager when calling your custom function.

Can I share a workbook containing LAMBDA functions with users on older Excel versions?

If you share a workbook using LAMBDA with someone using Excel 2019 or earlier, the function will not calculate properly. They will see a #NAME? error in the cells containing the custom formula.

Is it better to use a Pivot Table instead of LAMBDA for repeated SUMIFS?

Yes, in many structured data scenarios. If you need to summarize amounts across dozens of classes and types, inserting a Pivot Table is much faster, easier to maintain, and completely avoids the risk of formula syntax errors inherent in building multi-parameter LAMBDA or SUMIFS solutions.

Excel & Data Problems