using sumifs() to compare cumulative totals for actual versus budget based on financial pe

pls help...I am tearing my hair out!...well not literally:-)

I am creating a project report, which compares the actual costs vs the budget for each project. I obviously have all the budget figures for each financial period, and I want to compare the ytd cumulative figure based on the current month. I think I need to use a sumifs() statement, but am not sure where to start. I have attached a sample spreadsheet. I have over 100 projects to monitor, but have only included 8 sample projects.

any help would be appreciated

thanks Woodstock

Hi DILIPandey

Thank you very much for the suggestion. It is not quite what I was looking for, but it definitely works. I suppose I confused the matter by creating a cumulative amount at the bottom of the report. The current report contains over 130 projects and is growing...I manually calculated the cumulative amounts below the existing report, in the sample to indicate what the result would need to be. I could create an additional report which calculates the cumulative for the budget and actual (would need to be on separate sheet), but I was hoping to use a calculation to automatically calculate the cumulative based on the information in the range A2:BI13 (in the sample spreadsheet; in reality, it would be range A2:BI130).

I hope that makes sense.

rgds

Woodstock

I am doing the same exact exercise, but for some reason my formula returns a "0". any ideas?

Hi,

So I want to be able to look at the table in columns C:H, group the corrosponding catagories from column L and then return the sum of each for the specified month in N5.

Your help would be greatly appreciated!

Sorry, yes. I am new to this.

Hi DILIPandey
Sorry for taking such a long time to respond, but I have had a few deadlines I needed to meet for yesterday.

I think I confused the matter by creating running totals for each project in rows 16-26.

The formula you have given me

Formula:

 =INDEX(Budget!$A$16:$N$26,MATCH('comparison report'!$A5,Budget!$A$16:$A$26,0),MATCH('comparison report'!$B$3&'comparison report'!$D$3,Budget!$A$16:$N$16&Budget!$A$17:$N$17,0))

works brilliantly if using the running totals as the array (Budget!$A$16:$N$26) in the index function.

I am trying to remove the additional step of having to create a separate report which creates a running total by project for each year. I was therefore hoping to use the data in rows 6-13 to calculate the running total by project based on the financial period and month selected.

I have attached a revised sample with the running totals removed. I only included these in the first place to show what the result should be for a given month.

I hope I have managed to express myself more clearly.

Thank you for your patience.

Woodstock

Hi DILIPandey
C5 in comparison report sheet would be blank BUT because B6:E6 of budget sheet are blank, and
8639 in cell C9 of comparison report sheet because that is the sum of B9:E9 on the budget sheet.

In essence, I want it to sum each month for the given financial period for each project and stop at the current month. In this case October (D3 on comparison report sheet).
rgds
Woodstock

Hi DILIPandey

I have never used OFFSET before, so trying to understand the formula, but it works

Two questions for you:

1. when I look at the spreadsheet you attached, it works, but when I copy it into my spreadsheet, I get a #VALUE! error. Your formula on your spreadsheet has {}, whereas the formula in your post doesn't.
   
   Formula:

   {=SUM(OFFSET(Budget!$B6,0,0,1,MATCH('comparison report'!$D$3&'comparison report'!$B$3,Budget!$B$1:$BI$1&Budget!$B$2:$BI$2,0)))}
   
   

   
   I tried copy the formula from both the post and your attachment onto my sheet, but I get the same error.
2. the formula works for F13, but as soon as I change the financial period to F14, it still adds the figures from F13. ie if I select F14 and July (effectively month 1 for new financial year), cell B9 on the comparitive report should read 3990

rgds

Woodstock

sorry...typo...I was referring to the row below...having a look at your formula now...thanks again for your help...will let you know how I get on.

Woodstock

that is absolutely brilliant! thank you! I will try and replicate in my master sheet and come back to you if I have any hassles. Thank you.