Summing month to date of values in multiple columns

[oshodibo]

Hi all,

I am trying to sum up the month to date of values in cells.

I need to find a way to lookup current month date and sum values in multiple columns from the start of the fiscal(business cycle) year to current month of the year.

So if the current month is July then the year to date value is the sum of values in say column b to column h from January to July 2013.

The past year to date will be January to July 2013.

I have attached an example to give a bit more of a guide.

Any ideas?

[RobertMika]

B3 down confirm Control+Shift+enter:
=SUM(IF('Current year'!$B$1:$H$1<=$A$1,IF('Current year'!$A$2:$A$17=$A3,'Current year'!$B$2:$H$17)))
D3 down confirm Control+Shift+enter:
=SUM(IF('past year'!$B$1:$H$1<=$A$2,IF('past year'!$A$2:$A$17=$A3,'past year'!$B$2:$H$17)))

[rajeshturaha]

Hi,
welcome to the forum.

Plz see the attachment for your desired result. In analysis sheet only change the month as desired in a1, rest will be calculated automatically.

[Ace_XL]

In Row 3

Current year:
=SUM(INDEX('Current year'!$B$2:$B$17,MATCH($A3,'Current year'!$A$2:$A$17,0),0):INDEX('Current year'!$B$2:$H$17,MATCH($A3,'Current year'!$A$2:$A$17,0),MATCH($A$1,'Current year'!$B$1:$H$1,0)))

Past year:
=SUM(INDEX('past year'!$B$2:$B$17,MATCH($A3,'past year'!$A$2:$A$17,0),0):INDEX('past year'!$B$2:$H$17,MATCH($A3,'past year'!$A$2:$A$17,0),MATCH($A$2,'past year'!$B$1:$H$1,0)))

[oshodibo]

Hey Rob,

I have tried your formula but I am getting wrong summations.

let me add that the past year data is in a different tab ( monthly trial balance) so it needs to look up the relevant rows and expenses.

[Ursul]

@Ace_EL

Great formula BUT there's a problem when working on February because 'past year' cell C1 shows 29/02/2012, whereas Analysis A2 is revealing 28/02/2012 (UK formatting) so when MATCH($A$2,'past year'!$B$1:$H$1,0) is looking for an exact match it is not there! Changing to MATCH($A$2,'past year'!$B$1:$H$1,1) helps but not sure how this effects the rest of the formula?

Cheers,

[Ursul]

@Ace_EL

Sorry I should have picked this up but changing the MATCH as stated actually causes it to find Jan-2012 - so my suggested change was rubbish!! It appears your formula works fine if Past Year, Feb 2012 is changed to 28/02/2012 and not read 29/02/2012 - trust that is understandable!

[oshodibo]

Hey people,

the formula is not adding up the correct sum of months in the rows.
I have a monthly trial balance ( past year) and it needs to look up the corresponding expense/revenue in column A before summing up the correct year to date value .
Say "bank charges" or "insurance", could it locate insurance in the past year "monthly trial balance" tab- Column A and sum up the year to date value?

[oshodibo]

Hey people,

the formula is not adding up the correct sum of months in the rows.
I have a monthly trial balance ( past year) and it needs to look up the corresponding expense/revenue in column A before summing up the correct year to date value .
Say "bank charges" or "insurance", could it locate insurance in the past year "monthly trial balance" tab- Column A and sum up the year to date value?

[RobertMika]

You have been given few solutions.
If they still do not work ,can you state your expected outcome?

[oshodibo]

Hey Robert,

say the past year monthly trial balance I have posted also contains balance sheet accounts.

I would need the formulae to look up the right accounts against the analysis tab and add up the correct year to date value

[RobertMika]

I would repeat:
Please enter the required outcome;
The only diference I see is are the bold cells..

[oshodibo]

Hey Rob,

I have resubmitted the worksheet and indicated what it is not calculating correctly and my desired output. The problem is that the formula is adding up the asset accounts sequentially instead of looking up the correct accounts in the analysis ( column A).
I have highlighted the incorrect year to date values caused by this. I apologize for not stating the problem precisely.

[RobertMika]

mgmt%20anlysis(1).xlsx

Please see blue column.
Is this what you after?

[oshodibo]

Hey Rob!

Am at a loss for words!This is it! Great! Thank you for the time and tech help.

[RobertMika]

You are welcome.

[rajeshturaha]

Replacing formula in Col c

Please Login or Register  to view this content.

and in Col D

Please Login or Register  to view this content.

Might give you your desired result