• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Formulating End of Month

D

derek davis

Member
Hello everyone, could I have a little assistance please. I am trying to create a EOM gain total in excel. I have supplied an xlsx example. I have tried to use sumif with EOMonth, but the end result always seems to give "0". Here is the formula that I used
SUMIFS(A:A+F:F-$L$2,E:E,">="&N23+n2,E:E,"<="&EOMONTH(N23+n2,0)). The EOM gain total that I need is "A+F-L2" for each month of the year. I can't define the number of cells in "A",F:F or"E" as they are variable, so I used A:A and E:E. At the moment we are in year 2019 but 2020 which appears in "N52"is looming up and I will need to define the year somehow. When you open the sheet navigate to A75 for the raw data and look at O27 for where I need the result of the month gain total to appear. Thank you.
 

Attachments

  • test 2.xlsx
    73.1 KB · Views: 12
Your date formula part is returning a string. For starters, following edit gives £51.00 as answer in cell O27.
=SUMIFS(A:A,E:E,">="&DATEVALUE(N23&"-"&$N$2),E:E,"<="&EOMONTH(DATEVALUE(N23&"-"&$N$2),0))
 
Hi
shrivallabha, thanks for replying to me. I took a look at the formula that you offered and noted that it only sums "A". further in my original post asked the following,

"The EOM gain total that I need is "A+F-L2" for each month of the year. I can't define the number of cells in "A",F:F or"E" as they are variable, so I used A:A and E:E. At the moment we are in year 2019 but 2020 which appears in "N52"is looming up and I will need to define the new year somehow"

Can you assist?

Thank you.
 
Hi
shrivallabha, thanks for replying to me.Please would you ignore the previous post.
I took a look at the formula that you offered and noted that it sums "A". further in my original post asked the ,

"The EOM gain total that I need is the last day of the current month "A+F+J-$L$2" for each month of the year. I forgot to include the complete equasion, I am sorry to be so vaugue.

Thanks
 
You must log in or register to reply here.
Back
Top