Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • How to use Excel COLUMN Function
  • Approximate match with multiple criteria in Excel
  • How to get first column number in range in Excel
  • How to get relative column numbers in a range in Excel
  • How to get last row in numeric data in Excel

Data Analysis Examples

  • Conditional Formatting Color Scales Examples in Excel
  • How to Sort by Color in Excel
  • How to create Gauge Chart in Excel
  • Data Series in Excel
  • How to Create Thermometer Chart in Excel

Data Validation Examples

  • Excel Data validation date in specific year
  • Excel Data validation require unique number
  • Excel Data validation with conditional list
  • Excel Data validation must not contain
  • Excel Data validation allow uppercase only

How to conditionally sum numeric data in an Excel table using SUMIFS

by

To conditional sum numeric data in an Excel table, you can use SUMIFS with structured references to for both sum and criteria ranges.

Formula

=SUMIFS(Table[sum_col],Table[crit_col],criteria)

Explanation

In the example shown, the formula in I5 is:

=SUMIFS(Table1[Total],Table1[Item],H5)

Where Table1 is an Excel Table with the data range B105:F89.

Worked Example:   How to calculate current stock or inventory in Excel

How this formula works

This formula uses structured references to feed table ranges into the SUMIFS function.

Worked Example:   SUMIFS with multiple criteria and OR logic in Excel

The sum range is provided as Table1[Total], the criteria range is provided as Table1[Item], and criteria comes from values in column I.

The formula in I5 appears like this:

=SUMIFS(Table1[Total],Table1[Item],H5)

And resolves to this:

=SUMIFS(F5:F89,D5:D89,"Shorts")

The SUMIFS function returns 288, the sum values in the Total column where the value in the Item column is “Shorts”.

Worked Example:   Working With Tables in Excel

Post navigation

Previous Post:

How to calculate two-way lookup VLOOKUP in Excel Table

Next Post:

How to sum a total in multiple Excel tables

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others
  • How to compare two text strings in Excel
  • FIXED function: Description, Usage, Syntax, Examples and Explanation
  • Remove text by matching in a cell in Excel
  • How to display conditional message with REPT function in Excel
  • How to convert text string to array in Excel
  • ISOWEEKNUM function: Description, Usage, Syntax, Examples and Explanation
  • Find Last Day of the Month in Excel
  • Count dates in current month in Excel
  • How to get Weekdays, Working days between Two Dates in Excel
  • How to Calculate Age in Excel
  • XNPV function: Description, Usage, Syntax, Examples and Explanation
  • ODDLPRICE function: Description, Usage, Syntax, Examples and Explanation
  • PRICE function: Description, Usage, Syntax, Examples and Explanation
  • How to calculate simple interest in Excel
  • Example of Future value of annuity in Excel
Acronyms, Abbreviations, Initialism & What They Stand For
© 2021 xlsoffice. All Rights Reserved | Teal Smiles