Sum based on typed Month

[Assadniazi]

Hi,

I need help. I am preparing an Income statement, which contains number of month written in a row and in the row below I will write the income in those month. In the end of the the 12 months there is a total of all those months. So for example the rows looks like this:

Row 1: Jan, Feb, March, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec, Total
Row 2: 100, 100, 100 , 100,100, 100,100, 50, 100, 100, 100, 100 1150

I need some formula that lets say that I write the text: Jun, so the total row sums from Jan until Jun. If I change the text to Aug so the total takes the sum of Jan until August.

I have also put the attachment, I need the formula in the in cell O10 titled "YTD Budget 2016"


Please advise on this and I shall be very grateful.

Thanks
Assad

[Pete_UK]

There is no attachment.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

Note that the Paperclip icon does not work.

Pete

[Assadniazi]

Sorry I was not aware that attachment didnt get uploaded.

I have attached here.

Thanks

[Pete_UK]

Suppose you use cell O6 to specify the month you want to count up to. You should apply a custom format to this cell of "mmm" so that it only displays the month, but you should enter a proper date into the cell which is the first of the month that you are interested in, e.g. 1/06/2016 for June.

Then you can have this formula in cell O10:

=SUM($C10:INDEX($C10:$N10,MATCH($O$6,$C9:$N9,0)))

which will sum from C10 up to the column whose month you have in O6.

Hope this helps.

Pete

[Assadniazi]

Thank you sooooooooooo much!!!

I spent months looking here and there and asking people about it but couldnt get it!! People told me so much complex formulas.

This is so simple and it works efficiently!

Will forever be grateful!

Thanks!

[Assadniazi]

Thank you sooooooooooo much!!!

I spent months looking here and there and asking people about it but couldnt get it!! People told me so much complex formulas.

This is so simple and it works efficiently!

Will forever be grateful!

Thanks!

[Assadniazi]

Suppose you use cell O6 to specify the month you want to count up to. You should apply a custom format to this cell of "mmm" so that it only displays the month, but you should enter a proper date into the cell which is the first of the month that you are interested in, e.g. 1/06/2016 for June.

Then you can have this formula in cell O10:

=SUM($C10:INDEX($C10:$N10,MATCH($O$6,$C9:$N9,0)))

which will sum from C10 up to the column whose month you have in O6.

Hope this helps.

Pete

This is the simplest of all formulas for this purpose. It saved me 1 Day to do my monthly reporting!

[Pete_UK]

You're welcome - glad to help. Thanks for the rep.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post and mark this thread as SOLVED.

There are a number of different variations that you could use. For instance, if you just wanted to put the 3-letter variations of the month in O6, i.e. "Jan", "Feb", "Mar" etc. (without the quotes), then you could have this formula in O10 instead:

=SUM($C10:INDEX($C10:$N10,MATCH(DATEVALUE(1&$O$6&2016),$C9:$N9,0)))

That means less typing for you. Alternatively, you could just enter the month number in O6, formatted as General, such that 1 = January, 2 = February, and so on, then the formula in O10 could become:

=SUM($C10:INDEX($C10:$N10,$O$6))

which is probably the simplest formula.

Hope this helps.

Pete