Blog

Example 1: Sum rows that match values

Example 2: Sum rows that match values in a different sheet

Example 3: Advanced formulas

Additional Resources

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.

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")
```

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

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")
)
```

Download

Log In