Looking to see the Sum based on what is in another column

MaureenCondon

We are trying to move all of our excel sheets into smartsheets and we have one sales tracking sheet we are looking to have automated. The columns are Salesperson, Month, Expected Sales. What we want to see if Month equals March what is the Sum of the Expected Sales for those Salespersons. We want to have this automated so that as we add people to the list, we only need to add their info, we do not need to update any formulas for the Sum of the Expected Sales - anyone know if this is possible?

Jeff Reisman

@MaureenCondon

You can add a Summary field to the sheet that counts this, or place this formula in its own column.

I use SUMIFS for this. The syntax is =SUMIFS(range, criterion_range1, criterion1, criterion_range2​, criterion2​...)

• range — The group of cells to sum, assuming they meet all criteria.
• criterion_range1 — The group of cells to be evaluated by the criterion.
• criterion1 — The condition that defines which numbers to add; for example: 15, "Hello World!", or >25.
• criterion_range2, ​criterion2​... —[optional] Additional ranges and criteria to have evaluated.

If you want to total Expected Sales for March for ALL Salespersons, and your Month column actually spells out the month name:

=SUMIFS([Expected Sales]:[Expected Sales], Month:Month, "March")

(note - Columns with multiple words, numbers, or special characters in the column name need to be bracketed with square brackets.)

If you want to total Expected Sales for March for ALL Salespersons, and your Month column is a date field (note - 3 is the month number for March, the MONTH function evaluates the month number of a date value):

=SUMIFS([Expected Sales]:[Expected Sales], Month:Month, MONTH(@cell) = 3)