Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • Add Outline to Data in Excel
  • Reverse List in Excel
  • How to Create Area Chart in Excel
  • How To Create Frequency Distribution in Excel
  • Understanding Pivot Tables in Excel

References

  • Merge tables with VLOOKUP in Excel
  • Perform case-sensitive Lookup in Excel
  • How to retrieve first match between two ranges in Excel
  • How to use Excel VLOOKUP Function
  • How to get address of first cell in range in Excel

Data Validations

  • Excel Data validation number multiple 100
  • Excel Data validation specific characters only
  • Excel Data validation with conditional list
  • Excel Data validation must begin with
  • Excel Data validation only dates between

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.

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).

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.

Post navigation

Previous Post:

How to use Excel CHOOSE Function

Next Post:

Customize Ribbon 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

Logical Functions

  • TRUE function: Description, Usage, Syntax, Examples and Explanation
  • Not Equal To ‘<>‘ operator in Excel
  • Invoice status with nested if in Excel
  • IFS function: Description, Usage, Syntax, Examples and Explanation
  • IF, AND, OR and NOT Functions Examples in Excel

Date Time

  • Display the current date in Excel
  • Two ways to sum time over 30 minutes in Excel
  • How to calculate future date say 6 months ahead in Excel
  • EDATE function: Description, Usage, Syntax, Examples and Explanation
  • Add decimal hours to time in Excel

Grouping

  • How to randomly assign data to groups in Excel
  • Map inputs to arbitrary values in Excel
  • Map text to numbers in Excel
  • Calculate conditional mode with criteria in Excel
  • How to randomly assign people to groups in Excel

General

  • Subtotal invoices by age in Excel
  • Zoom Worksheet in Excel
  • Find, Trace and Correct Errors in Excel Formulas using ‘Formula Auditing’
  • 44 Practical Excel IF function Examples
  • How to generate random times at specific intervals in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning