SUMIF (Final piece of the puzzle)

Heath Sanders

 

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

J. Craig Williams

Heath,

 

What range are you summing?

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

 

Craig

Heath Sanders

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>

J. Craig Williams

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

Heath Sanders

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.

 

 

 

J. Craig Williams

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

 

Heath Sanders

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

 

J. Craig Williams

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