Function Problems

How to Sum Critical, High, Medium, and Low Severity Values in Excel

Maira Mehtab Sep 20, 2026 869 views

Question details

The user needs a dynamic formula to calculate totals for specific severity categories (Critical, High, Medium, Low) from a separate worksheet, while handling dynamic rows and excluding a grand total row.

Product: Excel
Device & OS: not provided
Scenario: Pulling specific severity totals from the 'STATS-HOST-SEVERITY' worksheet to a Scorecard sheet, ensuring zero is returned when there are no matches.
Observed behavior: The current dataset size changes dynamically and contains a Grand Total row that must be excluded from the final severity summation on the scorecard.

Before you start

Ensure that your source data worksheet ('STATS-HOST-SEVERITY') has clear column headers matching your scorecard criteria (e.g., Critical, High, Medium, Low) and confirm the range containing your values.

Solution 1Recommended

Use XLOOKUP and SUM to Total Severity Categories

Combine the SUM and XLOOKUP functions to dynamically find the correct severity column and add up all its values.

The XLOOKUP function is ideal for this scenario because it can return an entire array or range of values based on a single lookup criteria. By wrapping it in the SUM function, you can instantly total the entire matching column.

Select the destination cell

Click on cell B5 (or your designated target cell) in your Scorecard worksheet where you want the total to appear.

Enter the XLOOKUP and SUM formula

Type the formula: =SUM(XLOOKUP(A5,'STATS-HOST-SEVERITY'!$B$2:$E$2,'STATS-HOST-SEVERITY'!B3:E33))

Confirm the calculation

Press Enter. The formula will look for the exact severity label in cell A5, match it against the headers in the host sheet, and sum the corresponding rows.

Dynamic Referencing: Using cell references like A5 for your lookup value allows you to drag the formula down to automatically calculate High, Medium, and Low values.

Solution 2

Exclude the Grand Total Row Using the DROP Function

If your dynamically sized data includes a Grand Total row at the bottom, use the DROP function to omit it from the calculation.

Advanced Formula Support

Calculate Categorized Values Easily in WPS Spreadsheet

WPS Spreadsheet fully supports advanced dynamic array functions like XLOOKUP, DROP, and SUMPRODUCT, allowing you to calculate complex reports efficiently and accurately.

1. Open your report in WPS Spreadsheet: Launch WPS Office and open your workbook containing the Scorecard and Severity datasets.
2. Input the dynamic formula: Select the target cell on your Scorecard sheet and enter =SUM(DROP(XLOOKUP(A5,'STATS-HOST-SEVERITY'!$B$2:$E$2,'STATS-HOST-SEVERITY'!B3:E33),-1)).
3. Drag to fill: Press Enter to execute the formula, then click and drag the fill handle down to apply the exact same logic to your High, Medium, and Low rows.

100% compatible with Microsoft Excel formulas and functionsSupports dynamic arrays like XLOOKUP and DROP out of the boxLightweight application with fast data processing capabilities for large datasetsIntuitive interface for seamless data analysis and reporting

microsoft office alternative - wps office

Frequently Asked Questions

How do I return a zero instead of an error if no matches are found?

You can utilize the built-in [if_not_found] argument of the XLOOKUP function. Simply add a 0 as the fourth argument, like this: XLOOKUP(A5, range1, range2, 0). This ensures a zero is passed to the SUM function.

Can I use SUMPRODUCT instead of XLOOKUP for this task?

Yes. You can use SUMPRODUCT by multiplying the target range by a boolean array that checks the headers. For example: =SUMPRODUCT((HeaderRange=A5)*(DataRange)). This will sum only the columns where the header matches your criteria.

What exactly does the DROP function do in this formula?

The DROP function removes a specified number of rows or columns from the start or end of an array. In this case, using '-1' tells the program to strip off the very last row (the Grand Total) from the data retrieved by XLOOKUP before the SUM function adds it up.

Will this formula update automatically if more rows are added?

As long as the new rows fall within the defined range (e.g., B3:E33), the formula will automatically include them. If your data expands beyond row 33 frequently, consider formatting your source data as an Official Table to make the ranges completely dynamic.

Excel & Data Problems