SmartSheet: How to Use SUM With VLOOKUP
If you want to use SUM function with VLOOKUP in SmartSheet, you're probably trying to do one of these:
- Add up all the values in column A if column B matches a specific value.
- Add up all the values in column A if column B matches a value in column C.
- Add values from another sheet.
- Add values from another sheet, but only if certain rows or columns meet a condition.
The solution is to use the SUMIFS function. SUMIFS can handle all of these tasks, and if your situation is a little different, SUMIFS will probably still work for you.
It's a common misconception that if you need to lookup data from another sheet, then you must use VLOOKUP, but that's not true. SmartSheet has many ways to look up data from other sheets, and SUMIFS is one of the best.
Example 1: Sum rows that match values
In this example, we have a work log where employees track their hours. We want to calculate the total number of hours worked for each employee. The first three rows list the total hours for "Alice", "Bob", and "Carol". The other rows show the hours they worked.
To add the hours for each employee, we'll use this formula:
=SUMIFS([Hours Worked]:[Hours Worked], Employee:Employee, Employees@row)
This formula adds up the hours in the Hours Worked column, but only if the Employee name matches the name in the current row.
Using @row is better than writing a separate formula for each employee, like this:
=SUMIFS([Hours Worked]:[Hours Worked], Employee:Employee, "Alice")
=SUMIFS([Hours Worked]:[Hours Worked], Employee:Employee, "Bob")
=SUMIFS([Hours Worked]:[Hours Worked], Employee:Employee, "Carol")
Example 2: Sum rows that match values in a different sheet
In the first example, the work log and the totals were on the same sheet. Now, we've moved the work log to one sheet and the totals to another sheet.
We'll update the formula in the totals sheet to pull data from the work log sheet. In the totals sheet, we start typing =SUMIFS and click on Reference Another Sheet.
Next, add a reference to the entire Employee column from the work log sheet. Select the whole column so any new rows in the work log will be included.
Then add a reference to the Hours Worked column.
Now update your formula to use these two references and add up the values:
=SUMIFS({Employee Work Log Hours Worked}, {Employee Work Log Employee}, Employee@row)
Example 3: Advanced formulas
The examples above are simple, but the SUMIFS is very powerful. You can add as many ranges and conditions as you need.
For example, if you want to add hours only for certain days and months:
=SUMIFS([Hours Worked]:[Hours Worked],
Employee:Employee, Employee@row,
DayOfTheWeek:DayOfTheWeek, "Tuesday",
Month:Month, OR(@cell = "January", @cell = "March")
)
Additional Resources
