Function Problems

How to Sum Excel Rows When Two Criteria Are Both Allow

Ayan Masood Sep 25, 2026 869 views

Question details

The user needs to calculate the sum of amounts in specific rows, strictly evaluating two different columns to ensure both contain the value 'Allow' while ignoring rows containing 'Exclude'.

How to Sum Excel Rows When Two Criteria Are Both Allow

Product: Spreadsheet
Device & OS: not provided
Scenario: Filtering and summing data rows conditionally where two separate data fields (e.g., FS and GLC) must simultaneously meet a specific criteria using an AND logic.
Observed behavior: The user requires an updated formula that processes each row individually, ensuring that an amount is only included in the total if both corresponding criteria fields match the required value.

Before you start

Ensure your data is organized into clean columns without empty header rows, and note the exact cell ranges for your sum amounts and the two criteria columns.

Solution 1Recommended

Use the SUMIFS Function for Multiple AND Conditions

SUMIFS is the most straightforward and efficient function to sum values when multiple conditions must be met simultaneously across different columns.

The SUMIFS function is specifically designed to calculate totals based on one or more conditions. By design, it applies 'AND' logic, meaning every single criteria you outline in the formula must evaluate to true for that particular row to be included in the sum.

Select a result cell

Click on the blank cell where you want the final calculated total to be displayed.

Start the SUMIFS formula

Type `=SUMIFS(` to begin writing the function.

Input the sum range

Highlight or type the range of cells that contain the numbers you wish to sum (e.g., `C2:C100`), then type a comma.

Add the first criteria range and condition

Select your first criteria column (e.g., the FS column `A2:A100`), type a comma, and enter your criteria exactly as `"Allow"`, followed by another comma.

Add the second criteria range and condition

Select your second criteria column (e.g., the GLC column `B2:B100`), type a comma, and enter `"Allow"`.

Execute the formula

Add a closing parenthesis so the final formula looks like `=SUMIFS(AmountRange, FSRange, "Allow", GLCRange, "Allow")` and press Enter.

Use the SUMIFS Function for Multiple AND Conditions

Range Size Verification: Make sure your AmountRange, FSRange, and GLCRange all cover the exact same number of rows. If the ranges are asymmetrical, the formula will return a #VALUE! error.

Solution 2

Use the SUMPRODUCT Function for Advanced Row-by-Row Evaluation

SUMPRODUCT is a highly versatile alternative array function that handles row-by-row calculations and is excellent for complex logic that goes beyond basic SUMIFS capabilities.

Calculate complex totals effortlessly

Easily Handle Multiple Criteria Formulas Using WPS Spreadsheet

WPS Spreadsheet fully supports advanced functions like SUMIFS and SUMPRODUCT. With built-in formula wizards, it makes processing large datasets and conditionally calculating sums intuitive and fast.

1. Open your dataset: Launch WPS Spreadsheet and open the file containing your amounts and criteria.
2. Use the Formula Builder: Click on the 'Formulas' tab on the top ribbon and select 'Insert Function'.
3. Search for SUMIFS: Type 'SUMIFS' into the search bar and click 'OK' to open the guided parameter dialogue box.
4. Select ranges visually: Use your mouse to easily highlight your Sum_range, Criteria_range1, and Criteria_range2, entering "Allow" in the Criteria boxes.
5. Complete the calculation: Click 'OK'. WPS will automatically generate the correct syntax and instantly display your result.

100% compatibility with Microsoft Excel formulas, functions, and .xlsx formats.Intuitive Insert Function wizard to visually guide you through complex SUMIFS parameters.High performance and lightweight execution, seamlessly handling massive datasets without lag.Free to use with comprehensive data analysis tools out of the box.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my SUMIFS formula returning 0 or an error?

This most commonly occurs when the ranges used in the formula are not equal in size. Ensure that your amount range and both criteria ranges cover the exact same rows (for instance, all of them spanning from row 2 to 100). Also, check for trailing spaces in your cells that might prevent a match with the word 'Allow'.

Can I use cell references instead of hardcoding 'Allow' into the formula?

Yes. Instead of typing "Allow", you can reference a cell that contains the word. For example, if cell E1 contains 'Allow', your formula can be written as `=SUMIFS(C2:C100, A2:A100, E1, B2:B100, E1)`. This makes your formula dynamic if you ever want to change the criteria word.

How do I add a third condition to my SUMIFS formula?

You can continuously append new criteria pairs to a SUMIFS function. Simply add a comma after your second criteria, select your third criteria range (e.g., D2:D100), type a comma, and enter your third criteria (e.g., "Yes") before the final closing parenthesis.

What happens if one of the criteria cells is blank?

If a cell in either the FS or GLC range is blank (or contains 'Exclude' or any other text besides 'Allow'), the SUMIFS formula considers the AND condition to be FALSE for that row. Therefore, the amount in that specific row will simply be ignored and not added to your final sum.

Excel & Data Problems