Totals based on date and salesperson on another sheet

Josh Manring

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:

Chris Mondeau

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.

Josh Manring

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?

Josh Manring

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

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.

Josh Manring

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

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.