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

Lookup and Reference Examples

  • Count rows that contain specific values in Excel
  • How to use Excel TRANSPOSE Function
  • VLOOKUP function: Description, Usage, Syntax, Examples and Explanation
  • Two-way lookup with VLOOKUP in Excel
  • Extract data with helper column in Excel

Data Analysis Examples

  • Conflicting Multiple Conditional Formatting Rules in Excel
  • How to calculate correlation coefficient Correlation in Excel
  • Get column index in Excel Table
  • How to create Gauge Chart in Excel
  • Working With Tables in Excel

Data Validation Examples

  • Excel Data validation unique values only
  • Excel Data validation with conditional list
  • Excel Data validation must not contain
  • Excel Data validation number multiple 100
  • Excel Data validation require unique number

Sum by group in Excel

by

This tutorial shows how to Sum by group in Excel using the example below;

Formula

=IF(B5=B4,"",SUMIF(B:B,B5,C:C))

Explanation

To subtotal data by group or label, directly in a table, you can use a formula based on the SUMIF function.

In the example shown, the formula in D5 is:

=IF(B5=B4,"",SUMIF(B:B,B5,C:C))

Note: data must be sorted by the grouping column to get sensible results.

Worked Example:   Sum if not blank in Excel

How this formula works

The framework of this formula is based on IF, which tests each value in column B to see if its the same as the value in the “cell above”. When values match, the formula returns nothing (“”). When values are different, the IF function calls SUMIF:

SUMIF(B:B,B5,C:C)

In each row where SUMIF is triggered by IF, SUMIF calculates a sum of all matching rows in column C (C:C). The criteria used by SUMIF is the current row value of column B (B5), tested against all of column B (B:B).

Worked Example:   Sum lookup values using SUMIF in Excel

Full column references like this are cool and elegant, since you don’t have to worry about where the data begins and ends, but you need to be sure there’s not extra data above or below the table that may get caught by SUMIF.

Worked Example:   Sum if equal to one of many things in Excel

Post navigation

Previous Post:

3D SUMIF for multiple worksheets in Excel

Next Post:

Subtotal invoices by age in Excel

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
  • Remove line breaks in a cell in Excel
  • How to create email address with name and domain in Excel
  • TRIM function: Description, Usage, Syntax, Examples and Explanation
  • How to split text with delimiter in Excel
  • How to convert numbers to text using TEXT function in Excel
  • How to calculate Next working/business day in Excel
  • Get last weekday in month in Excel
  • WORKDAY.INTL function: Description, Usage, Syntax, Examples and Explanation
  • Find Last Day of the Month in Excel
  • Calculate total hours that fall between two times in Excel
  • NPV function: Description, Usage, Syntax, Examples and Explanation
  • PPMT function: Description, Usage, Syntax, Examples and Explanation
  • SYD function: Description, Usage, Syntax, Examples and Explanation
  • Calculate cumulative loan interest in Excel
  • ODDLPRICE function: Description, Usage, Syntax, Examples and Explanation
© 2022 xlsoffice . All Right Reserved. | Teal Smiles