Totals based on date and salesperson on another sheet

Good evening,

I'm looking for some assistance, any help would be appreciated. I'm trying to total the "Install Amount" from my "2020 Sales Tracking Sheet" for each "Account Executive" by "Month" on my "Test - Sales Bonus Tracking" sheet. Both are shown below:


Answers

  • Chris Mondeau
    Chris Mondeau ✭✭✭✭✭✭
    edited 02/04/20

    Hi Josh! Try using this formula in your Sales Bonus tracking sheet for the install amount column.

    {Install Amount} , {Account Executive(s)}, and {Sold Date}= are external sheet references to the matching columns in your Sales tracking sheet. You'll need to make these sheet references first before the formula can work.

    =SUMIFS({Install Amount}, {Sold Date}, MONTH(@cell) = 1, {Account Executive(s)}, [Account Executive]@row)

    You'll need to change the Month(@cell) to the corresponding month number (may=5) to total.

  • Thanks cmondo!

    This is what it looks like now: =SUMIFS({Install Amount 1}, {Sold Date 1}, MONTH(@cell) = 1, {Account Executive 1}, [Account Executive]@row)

    It's coming up unparseable. Any ideas?

  • For whatever reason, I seem to be unable to get MONTH to work. This is working to total the [Install Amount] by date (I'm back to working on this on the same sheet as the data eliminating the cross sheet reference until I get the formula worked out:

    =SUMIFS([Install Amount]:[Install Amount], [Sold Date]:[Sold Date], AND(@cell >= DATE(2020, 1, 1), @cell < DATE(2020, 2, 1)))

    I cannot seem to get the next part down to additionally filter it by [Account Executive]

  • Chris Mondeau
    Chris Mondeau ✭✭✭✭✭✭

    Just checking, your external references have the entire column selected:

    For your second formula try using:

    =SUMIFS([Install Amount]:[Install Amount], [Account Executive(s)]:[Account Executive(s)], [Account Executive(s)]@row, [Sold Date]:[Sold Date], >=DATE(2020, 1, 1), [Sold Date]:[Sold Date], <DATE(2020, 2, 1))

    With this one you'll need to change the date range to affect the rows you want to sum.

  • Thanks. Just to clarify, the entry bolded below would be the value from the field correct? In this case, the first and last name of my Account Executive.

    =SUMIFS([Install Amount]:[Install Amount], [Account Executive(s)]:[Account Executive(s)], [Account Executive(s)]@row, [Sold Date]:[Sold Date], >=DATE(2020, 1, 1), [Sold Date]:[Sold Date], <DATE(2020, 2, 1))

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You can also try something like this. It will allow you to enter a month and year number instead of having to figure out if this month has 28, 29, 30, or 31 days.


    =SUMIFS([Install Amount]:[Install Amount], [Account Executive(s)]:[Account Executive(s)], "John Doe", [Sold Date]:[Sold Date], AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = 2020))


    This will give you John Doe's total for January of 2020.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!