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

Data Analysis

  • Understanding Anova in Excel
  • Number and Text Filters Examples in Excel
  • Conditional Formatting New Rule with Formulas in Excel
  • How to calculate average last N values in a table in Excel
  • Move chart to a separate worksheet in Excel

References

  • Offset in Excel
  • VLOOKUP function: Description, Usage, Syntax, Examples and Explanation
  • LOOKUP function: Description, Usage, Syntax, Examples and Explanation
  • Merge tables with VLOOKUP in Excel
  • Get nth match with INDEX / MATCH in Excel

Data Validations

  • Excel Data validation must contain specific text
  • Excel Data validation only dates between
  • Excel Data validation allow uppercase only
  • Excel Data validation date in next 30 days
  • Excel Data validation unique values only

Sum matching columns in Excel

by

This tutorial shows how to sum matching columns in Excel using the example below;

Formula

=SUMPRODUCT(data*(headers=A1))

Explanation

To sum values in columns by matching matching column headers, you can use a formula based on the SUMPRODUCT function. In the example shown, the formula in J5 is:

=SUMPRODUCT(data*(LEFT(headers)=J4))

where “data” is the named range C5:G14, and “headers” is the named range C4:G4.

The formula sums columns where headers begin with “a” and returns 201.

Worked Example:   Check if multiple cells have same value with case sensitive in Excel

How this formula works

At the core, this formula relies on the SUMPRODUCT function to sum values in matching columns in the named range “data” C5:G14. If all data were provided to SUMPRODUCT in a single range, the result would be the sum of all values in the range:

=SUMPRODUCT(data) // all data, returns 387

To apply a filter by matching column headers – columns with headers that begin with “A” – we use the LEFT function like this:

LEFT(headers)=J4) // must begin with "a"

This expression returns TRUE if a column header begins with “a”, and FALSE if not. The result is an array:

{TRUE,TRUE,FALSE,FALSE,TRUE,FALSE}

You can see that values 1,2, and 5 correspond to columns that begin with “a”.

Worked Example:   Count unique text values in a range in Excel

Inside SUMPRODUCT, this array is multiplied by “data”. Due to broadcasting, the result is a two-dimensional array like this:

{8,10,0,0,7,0;9,10,0,0,10,0;8,6,0,0,6,0;7,6,0,0,6,0;8,6,0,0,6,0;10,11,0,0,7,0;7,8,0,0,8,0;2,3,0,0,3,0;3,4,0,0,4,0;7,7,0,0,4,0}

If we visualize this array in a table, it’s easy to see that only values in columns that begin with “a” have survived the operation, all other columns are zero. In other words, the filter keeps values of interest and “cancels out” the rest:

Worked Example:   Count matches between two columns in Excel
A001 A002 B001 B002 A003 B003
8 10 7
9 10 10
8 6 6
7 6 6
8 6 6
10 11 7
7 8 8
2 3 3
3 4 4
7 7 4

With only a single array to process, SUMPRODUCT returns the sum of all values, 201.

Post navigation

Previous Post:

44 Practical Excel IF function Examples

Next Post:

Large with criteria 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

  • NOT function: Description, Usage, Syntax, Examples and Explanation
  • IFS function: Description, Usage, Syntax, Examples and Explanation
  • How to use IFS function in Excel
  • Extract multiple matches into separate rows in Excel
  • TRUE function: Description, Usage, Syntax, Examples and Explanation

Date Time

  • Dynamic date list in Excel
  • How to calculate percent of year complete in Excel
  • Get last weekday in month in Excel
  • Convert date to text in Excel
  • Convert Excel time to decimal seconds

Grouping

  • Group numbers at uneven intervals in Excel
  • Map text to numbers in Excel
  • Categorize text with keywords in Excel
  • How to randomly assign people to groups in Excel
  • Group arbitrary text values in Excel

General

  • How to Create Calendar in Excel
  • Print Excel Sheet In Landscape Or Portrait
  • Find, Trace and Correct Errors in Excel Formulas using ‘Formula Auditing’
  • How to calculate percentage discount in Excel
  • Create dynamic workbook reference to another workbook in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning