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

Data Analysis

  • How to calculate correlation coefficient Correlation in Excel
  • How To Filter Data in Excel
  • How to create running total in an Excel Table
  • Conditional Formatting New Rule with Formulas in Excel
  • Working With Tables in Excel

References

  • How to get last row in mixed data with blanks in Excel
  • How to get relative column numbers in a range in Excel
  • Left Lookup in Excel
  • Create hyperlink with VLOOKUP in Excel
  • Find closest match in Excel

Data Validations

  • Excel Data validation date in specific year
  • Excel Data validation whole percentage only
  • How To Create Drop-down List in Excel
  • Excel Data validation with conditional list
  • Excel Data validation must contain specific text

Sum lookup values using SUMIF in Excel

by
This tutorial shows how to Sum lookup values using SUMIF in Excel using the example below;

Formula

=SUMPRODUCT(SUMIF(codes,lookups,values))

Explanation

To sum values retrieved by a lookup operation, you can use SUMPRODUCT with the SUMIF function.

In the example shown, the formula in H5 is:

=SUMPRODUCT(SUMIF(codes,C5:G5,values))

Where codes is the named range J4:J5 and values is the named range K4:K5.

Context

Sometimes you may want to sum multiple values retrieved by a lookup operation. In this example, we want to sum holiday time taken each week based on a code system, where F = a full day, and H = a half day. If a day is blank, no time was taken.

The challenge is to find a formula that both looks up and sums the values associated with F and H.

How this formula works

The core of this formula is SUMIF, which is used to lookup the correct values for F and H. Using SUMIF to lookup values is a more advanced technique that works well when the values are numeric, and there are no duplicates in the “lookup table”.

The trick in this case is that the criteria for SUMIF is not a single value, but rather an array of values in the range C5:G5:

=SUMPRODUCT(SUMIF(codes,C5:G5,values))

Because we are giving SUMIF more than one criteria, SUMIF will return more than one result. In the example shown, the result of SUMIF is the following array:

{1,0.5,0,0,0}

Note that we correctly get 1 for each “F” and 0.5 for each “H”., and blank values in the week generate zero.

Finally, we use SUMPRODUCT to add up the values in the array returned by SUMIF. Because there is only a single array, SUMPRODUCT simply returns the sum of all values.

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

  • Not Equal To ‘<>‘ operator in Excel
  • How to use Excel TRUE Function
  • How to use Excel AND Function
  • Excel If, Nested If, And/Or Criteria Examples
  • OR function: Description, Usage, Syntax, Examples and Explanation

Date Time

  • Display the current date and time in Excel
  • Get project midpoint in Excel
  • NETWORKDAYS function: Description, Usage, Syntax, Examples and Explanation
  • Convert Excel time to decimal minutes
  • NOW function: Description, Usage, Syntax, Examples and Explanation

Grouping

  • Group numbers at uneven intervals in Excel
  • Group times into 3 hour buckets in Excel
  • Map inputs to arbitrary values in Excel
  • Group arbitrary text values in Excel
  • If cell contains one of many things in Excel

General

  • How to calculate percent change in Excel
  • Basic numeric sort formula in Excel
  • Check if range contains a value not in another range in Excel
  • How to calculate percentage discount in Excel
  • How to Create Calendar in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning