Excel Sum by Category: Formulas and PivotTables

Formulas & functions · Updated November 18, 2026

To sum values by category in Excel, use SUMIF: with category names in A2:A100, amounts in B2:B100, and the category to total in D2, enter =SUMIF($A$2:$A$100,D2,$B$2:$B$100). Excel adds the amounts whose category matches D2; use a PivotTable instead when you want to summarize several categories at once.

What “sum by category” means

A category is a label shared by multiple records, such as a product type, department, sales region, or expense class. Summing by category means adding the numeric values associated with each label rather than calculating one total for the entire list. For example, a sales table might contain many transactions for Coffee, Tea, and Pastry. A category summary gives one total for each product type.

The source data is usually arranged in columns: one column contains the category and another contains the amount. Each row represents one transaction or record. A formula or PivotTable groups matching labels and adds the corresponding numbers. The key is that the category and amount on a row must describe the same record.

Suppose the category-and-amount rows are Coffee, 120; Tea, 60; Coffee, 80; Tea, 40; and Pastry, 30. The category totals are Coffee 200, Tea 100, and Pastry 30. These totals add to 330, the same as the five source amounts combined. Checking that the group totals reconcile to the source total is a useful way to catch omissions or mismatched ranges.

Use SUMIF for one category condition

SUMIF is the straightforward choice when each row has one category and you need a total for one chosen category. Its arguments are the cells to check, the condition to match, and the cells to add. The category and amount ranges should cover corresponding rows; if one range starts on row 2, the other should start there too.

With category names in A2:A100, amounts in B2:B100, and the category you want to summarize in D2, enter =SUMIF($A$2:$A$100,D2,$B$2:$B$100). If D2 contains Coffee, the example data above returns 200. The dollar signs keep the source ranges fixed if you copy the formula down, while D2 changes to D3, D4, and so on.

For a small summary, type the category names into column D and place the formula in E2. Copy E2 down alongside the list. Use the same spelling in the summary labels as in the source data, or choose the label directly from the source to avoid typing differences. A category with no matching rows returns zero.

The sum range is optional if the cells being tested are also the numbers to add, but it is usually clearer to provide it explicitly. In a typical category summary, the test range contains text labels and the sum range contains amounts, so both ranges are needed.

Use SUMIFS for more than one condition

Use SUMIFS when the total must satisfy multiple conditions, such as a category and a region, or a category within a date period. Unlike SUMIF, its sum range comes first, followed by pairs of criteria ranges and criteria. Each criteria range must have the same dimensions as the sum range.

With categories in A2:A100, amounts in B2:B100, regions in C2:C100, the requested category in E2, and requested region in F2, enter =SUMIFS($B$2:$B$100,$A$2:$A$100,E2,$C$2:$C$100,F2). If E2 is Coffee and F2 is North, the formula adds only the amounts from rows where both conditions match. This is useful for questions such as “How much did each department spend in each region?”

For a date range, criteria can use comparison operators. With categories in A2:A100, amounts in B2:B100, transaction dates in C2:C100, the requested category in E2, the first date in F2, and the day after the final date in G2, enter =SUMIFS($B$2:$B$100,$A$2:$A$100,E2,$C$2:$C$100,">="&F2,$C$2:$C$100,"<"&G2). The formula includes dates on or after F2 and excludes dates on or after G2. If G2 is the first day of the next month, this includes the full month, including any time values stored with the dates.

Excel’s formula separator depends on regional settings. The examples use commas, as in the standard English-language syntax. Some installations expect semicolons instead; if Excel rejects a formula with a separator error, check the separator used by your version rather than changing the function name.

Build a category summary with a PivotTable

A PivotTable is often more convenient than a formula when you need totals for every category, want to rearrange the summary, or need to compare categories across another field. It creates an interactive report from the source records, without requiring a separate formula for each category.

  1. Make sure the source has one header row, no completely blank rows or columns inside the data, and a distinct column for each field, such as Category, Amount, Region, and Date.
  2. Select a cell in the source data, then choose Insert > PivotTable. Confirm the table or range and choose where to place the PivotTable.
  3. In the PivotTable Fields pane, drag Category to Rows and Amount to Values. Excel should summarize numeric amounts by Sum.
  4. If Excel shows Count instead of Sum, inspect the amount column for text values or inconsistent entries, then change the Values field settings to Sum if the data can be summed.
  5. To compare totals by region, drag Region to Columns or place it beneath Category in Rows. Drag Date to Filters if you want a report-level date filter.

PivotTables are especially helpful when categories change or when the same data needs several views. You can filter, expand, and rearrange fields without rewriting formulas. However, a PivotTable does not necessarily update the moment source cells change. Refresh it from the PivotTable controls or its right-click menu after changing the source data. If new records are added, an Excel Table used as the source can make the source range easier to maintain; confirm the PivotTable source includes the added rows before refreshing.

Choose between formulas and a PivotTable

Both approaches can produce accurate category totals. Choose based on how the result will be used, rather than assuming one method is always better.

  • Use SUMIF for a compact report with one condition, a fixed set of summary rows, or totals that need to sit beside other calculations.
  • Use SUMIFS when the definition of a group includes more than one condition, such as category plus region or category plus date boundaries.
  • Use a PivotTable to explore many categories, change the layout quickly, or compare totals across fields without building a large formula grid.
  • Use both when a PivotTable is useful for exploration but a fixed formula-based figure is needed elsewhere in a worksheet.

Formula summaries recalculate as their referenced cells change, subject to Excel’s calculation settings. PivotTables provide a flexible view but rely on refreshes to reflect source changes. For a recurring report, consider who will update it, whether categories are predictable, and whether readers need to rearrange the result.

Prepare data and keep summaries reliable

Consistent source data matters more than the particular summary method. Use one record per row and one type of information per column. Avoid merging cells or inserting subtotal rows into the source list, because these layouts can interfere with grouping, filtering, and expansion of the data range.

Convert the source range to an Excel Table with Insert > Table if it will grow over time. Give the table and its columns meaningful names, such as Sales, Category, and Amount. Structured references can make formulas easier to read. With a table named Sales, a Category column, an Amount column, and the criterion in E2, enter =SUMIF(Sales[Category],E2,Sales[Amount]). The table references expand as rows are added to the table.

Keep amounts as numbers and dates as real Excel dates, not text that only looks numeric or date-like. Apply number formats to control display, such as currency or date formatting, rather than typing currency symbols into amount cells. A value stored as text may be ignored by a sum or cause a PivotTable to count rather than sum records. If a cell is left-aligned unexpectedly or does not behave like other amounts, check its underlying value and type.

Common problems and how to fix them

A result of zero does not always mean the category has no transactions. The criteria may differ from the source label because of an extra space, a spelling variation, or a different text value. Check a source cell and the summary criterion closely. If labels come from manual entry, standardize them with a dropdown list or clean the source values before creating the report.

SUMIF is generally not case-sensitive, so it treats Coffee and COFFEE as the same text for matching. It does distinguish other differences, such as an added space or a different word. A criterion can include wildcards: an asterisk matches any sequence of characters and a question mark matches one character. With category text in A2:A100 and values in B2:B100, =SUMIF($A$2:$A$100,"*East*",$B$2:$B$100) adds rows whose category text contains East. Use wildcards carefully: a broad match can combine labels you meant to keep separate.

Check that the category and sum ranges have matching start and end rows. A range that omits the latest transactions will produce a plausible but incomplete total. When copying formulas, confirm the source ranges are absolute references, as shown by dollar signs, and the criteria cell is allowed to move down the summary list.

If a PivotTable displays Count of Amount instead of Sum of Amount, Excel may have detected text or blanks in the values field. Correct the source data, then refresh the PivotTable. Also verify that the value field is configured as Sum. A PivotTable can show a count for a field even when most entries appear numeric if some values are stored inconsistently.

Frequently Asked Questions

How do I sum only one category in Excel?
Use SUMIF with the category column as the criteria range and the amount column as the sum range. Put the category to match in a cell, then refer to that cell as the formula’s criteria.

What is the difference between SUMIF and SUMIFS?
SUMIF handles one condition, while SUMIFS can require several conditions at once. In SUMIFS, the sum range is the first argument; in SUMIF, it is the third argument.

Can Excel sum by category and month?
Yes. Use SUMIFS with one condition for the category and date criteria that mark the start and end of the month. Using a start date and the first day of the next month as an exclusive upper boundary also handles date cells containing times.

Why does my SUMIF formula return zero?
Check for a misspelled category, extra spaces, mismatched range sizes, or amounts stored as text. Also confirm that the formula’s ranges include the rows containing the records you expect to count.

Can a PivotTable update automatically when I add rows?
A PivotTable generally needs to be refreshed to show changed source values. Use an Excel Table as the source to help include added records, then verify the PivotTable source and refresh the report.

Which method should you start with?

For one category total, start with SUMIF; for totals defined by several conditions, use SUMIFS. If you want a complete, rearrangeable breakdown across many categories, create a PivotTable. Whichever you choose, check that the source labels are consistent, the amounts are numeric, and the summary totals reconcile with the data you intended to include.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.