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)

Heath Sanders
edited 12/09/19 in Archived 2017 Posts


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
    J. Craig Williams ✭✭✭✭✭✭
    edited 02/26/17



    What range are you summing?

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



  • Heath Sanders
    edited 02/26/17

    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
    J. Craig Williams ✭✭✭✭✭✭



    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$?



  • 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
    J. Craig Williams ✭✭✭✭✭✭


    [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.




  • 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
    J. Craig Williams ✭✭✭✭✭✭



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




This discussion has been closed.