Calculate ONLY if before the 10th day of month

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

  • Glad to hear this could fix your problem :)


    Thanks for your Thanks ... AND for the Like :thumbup:

    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

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!