Function Problems

How to Use Excel IF and SUMIF Formulas for Uniform Sizes

Maira Mehtab Sep 27, 2026 873 views

Question details

The user needs to construct correct Excel formulas to conditionally return values and calculate total quantities for specific uniform sizes in an inventory table.

Product: Excel
Device & OS: not provided
Scenario: Managing a uniform inventory across multiple sheets, requiring a master table to display total issued or received quantities for chosen sizes (e.g., Medium, Large).
Observed behavior: The user attempted to combine functions incorrectly by writing IF(E2:E18="Medium",SUM(F2:F18)), resulting in calculation errors instead of the correct sum of values.

Before you start

Ensure that the text values for sizes in your dataset (like "Medium" or "Large") have no hidden trailing spaces, as formulas look for exact text matches.

Solution 1

Use the IF Function to Conditionally Return a Value

Use this basic logical formula to extract an issued or received value into a single cell only when the specific uniform size matches your criteria.

The IF function checks whether a condition is met and returns one value if true, and another if false. This is ideal for row-by-row checks rather than calculating totals.

Select the target cell

Click on the cell in your current sheet where you want the returned value to be displayed.

Input the IF formula

Type the formula: =IF('Uniform Issued'!U2="Medium",'Uniform Issued'!V2,""). Ensure you include the quotation marks around the word Medium.

Apply the formula

Press Enter. If cell U2 on the 'Uniform Issued' sheet contains "Medium", the cell will display the value from V2. Otherwise, it will remain blank.

Solution 2Recommended

Use the SUMIF Function to Calculate Total Amounts by Size

Use the SUMIF function to aggregate total quantities from an entire column based on a specific criteria, such as a selected uniform size.

Manage Spreadsheets Easily

Master Logical and Math Formulas with WPS Spreadsheet

WPS Spreadsheet provides a fully equipped, user-friendly environment for managing inventory data. It fully supports standard data analysis functions, including IF, SUMIF, and SUMIFS, to help you calculate complex datasets accurately.

1. Open your inventory file: Launch WPS Spreadsheet and open your existing uniform inventory workbook.
2. Access the Formulas tab: Navigate to the Formulas tab on the top ribbon to explore the Insert Function tool for logical and mathematical calculations.
3. Use formula hints: Type =SUMIF( in any cell to trigger the on-screen formula guide, which visually highlights the required range, criteria, and sum_range arguments.

100% compatible with Microsoft Excel (.xlsx) formats and formulasIntelligent formula autocomplete and built-in syntax hintsLightweight performance for handling large multi-sheet inventory workbooksFree to use for everyday data management and calculation tasks

microsoft office alternative - wps office

Frequently Asked Questions

Why does my SUMIF formula return 0 even though there are matching sizes?

This commonly happens when there are hidden spaces in your data. "Medium " (with a space) does not equal "Medium". Check your size column for trailing spaces and use the TRIM function if necessary to clean your data.

How do I calculate the total for multiple sizes, like Medium and Large together?

You can add two SUMIF functions together by writing =SUMIF(E2:E18,"Medium",F2:F18) + SUMIF(E2:E18,"Large",F2:F18). Alternatively, you can use the SUM(SUMIFS(...)) array technique for a cleaner formula.

Can I use SUMIF to pull data from a completely different sheet?

Yes. When writing the formula, you can click on the tab of the other sheet to select your ranges. The formula will automatically add the sheet name reference, looking like =SUMIF('Uniform Issued'!E2:E18,"Medium",'Uniform Issued'!F2:F18).

What is the difference between SUMIF and IF combined with SUM?

SUMIF is a dedicated function designed to calculate a total conditionally across an array of cells. Combining IF with SUM (e.g., IF(range=criteria, SUM(range))) is not standard syntax and usually evaluates a single condition rather than iterating through an entire column.

Excel & Data Problems