Calculate ONLY if before the 10th day of month

  • Hello


    please see file attached


    need to sum the profit for that year using company and month.


    hard part is only sum that month based on the live date column, if the date is before the 10th you count that month into the totals


    thanks

  • Hello,


    If I am not mistaken you are working with monthly numbers ... with no daily details ...


    So how can you expect to split your amounts ... ' after the 10th day ' ... ????(

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • im not sure what you mean.


    Jan - Dec can be labelled as 1-12 if needed.


    so for example


    first line of data


    AAA live date is 12/04. as this is on the 12th day of the month, i will only be summing the 5th-12th month


    if AAA live date was before the 10th day of the month i will sum all months including that month.

  • If my understanding of your requirement is OK ...


    see attached test file ...


    Let me have your comments ...


    Hope this will help

    :)

    Files

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Another option using Sum + Offset function


    In D4, formula copied down :


    =SUM(OFFSET(F$3,MATCH(B4,$F$4:$F$9,0),MONTH(C4)+(DAY(C4)>10),1,13+(DAY(C4)>10)-MONTH(C4)))


    Remark :


    It is better to add ….MATCH(B4,$F$4:$F$9,0)… inside the Offset formula to find the Row position of the Company name in the source list


    Regards

  • yes the company position isnt a perfect match like the example i gave.


    AAA could be in different rows, as they all could so it works perfect


    i really got to learn this formula. very very useful :)


    thanks all