#### Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

# SUMIF (Final piece of the puzzle)

Options
edited 12/09/19

The sale been made and fits the criteria (Sold By) Peter, (Status) WON, (Result) 100%, (Current Month Sales) this month/yer date, it returns dollar value.

The job has been invoiced, (Invoiced Date) todays date 25/02/17 (Nz Time) returns (Comms Due Date) 20th of the following month, in this case 20/03/17.

So when (Current Month Comms), the month and year we are in equals (Comms Due Date) then it returns the dollar value  (Value of Jobs) *0.002.

So when the commission date to be paid is 20/03/17 and the calendar rolls over to March 2017 then current month commissions will calculate value of jobs and calculate 2% of it.

=SUMIFS([Comms Due Date]2, IFERROR(MONTH(@cell), "") = MONTH(TODAY()), [Comms Due Date]2, IFERROR(YEAR(@cell), "-") = YEAR(TODAY()))

• ✭✭✭✭✭✭
edited 02/26/17
Options

Heath,

What range are you summing?

The first argument of SUMIFS() should be that range.

Craig

• edited 02/26/17
Options

Hi Craig,

I started to put together the sum, then realised i was getting out of my depth. "Current Month Comms"  will calulate "Value of Jobs" when in matches the date MONTH/YEAR of "Comms Due Date" . Shared sheet, so you can see the logic of it.

<IFRAME WIDTH=1000 HEIGHT=700 FRAMEBORDER=0 SRC="https://app.smartsheet.com/b/publish?EQBCT=bdc585390e8b43d1842a97e0a680e00e"></IFRAME>

• ✭✭✭✭✭✭
Options

Heath,

Isn't the [Current Month Comms] cells just

=[Current Month Sales]2 *0.002

for row 2 with the formula then copied to the rest of the column?

You've already done all the logic in the other columns.

At least, I think so.

And to be clear 0.0002 is 0.02%, not 2% so for a 10000 order, your commision is 2 NZ\$?

Craig

• Options

Hi Craig,

Stage 1

[Current Month Sales] looks at the sold date, if its in the current month then it returns [Value of Jobs] if not, its blank.

Stage 2

[Comms Due Date] looks at [Invoiced Date] , which returns 20th of following month.

When [Current Month Comms] matches the[Comms Due Date] MONTH/YEAR (Not day, not relevant), then it needs to look at [Value of Jobs] and times it by 2%. This is so the [Comms Due Date] (Commissions) returns the dollar value from [Value of Jobs] and prompts me that we are in that month that needs to be paid.

If [Sold Date] is different to [Invoiced Date] then it returns black which is why =[Current Month Sales]2 *0.002 can’t work or doesn’t help with the final solution.

• ✭✭✭✭✭✭
Options

Ah.

[Current Month Sales] will show items sold this month.

[Curent Month Comms] will show items invoiced in the previous month.

In your current data shared to us, there should be nothing in the [Current Month Comms], correct?

If row 2 was invoiced on Jan 31st, you would expect [Current Month Comms] to be \$0.06 in February and blank otherwise.

Try this:

=IF(ISDATE([Comms Due Date]2), IF(MONTH([Comms Due Date]2) = MONTH(TODAY()), [Value of Jobs]2 * 0.0002, ""), "")

If you need to worry about YEAR() too, just add an AND() for that in the second IF.

Craig

• Options

Nice work Craig, got there. Had to drop zeros to get 2%. Happy to share if anyone is looking for the same platform to work from.

=IF(ISDATE([Comms Due Date]2), IF(MONTH([Comms Due Date]2) = MONTH(TODAY()), [Value of Jobs]2 * 0.02, ""), "")

My attempt at adding in year below..., Have i cracked it ?

=IF(ISDATE([Comms Due Date]2), IF(MONTH(AND(YEAR(),([Comms Due Date]2) = MONTH(TODAY()), [Value of Jobs]2 * 0.02, ""), "")

• ✭✭✭✭✭✭
Options

Nope.

=IF(ISDATE([Comms Due Date]2), IF(AND(MONTH([Comms Due Date]2) = MONTH(TODAY()), YEAR([Comms Due Date]2) = YEAR(TODAY())), [Value of Jobs]2 * 0.02, ""), "")

AND (  check1 , check2 )

Craig

This discussion has been closed.