Rolling total

[jojoluv377]

What I am trying to do is keep track of billed and unbilled revenue from month to month by employee & contract. I have created a workbook with 12 worksheets (Jan-2013 through Dec-2013).

In the January workbook, I am hard coding the December 2012 ending balances, but I can't figure out what formula to use to make sure the correct contract and employee are updated when new the next month is updated.

For example I need a formula that performs this evaluation:
Sum Jan-2013!AF1{unbilled balance Jan 2013}+(Feb-2013!Z1*AA1)-AC1{(Hrs Wrkd*rate)-Feb-2013 billed} if B2 (the contract #) and M1 (the employee's name)


This is the formula I currently have {=('FEB-2013'!Z1*'FEB-2013'!AA1+'FEB-2013'!AB1)-('FEB-2013'!AC1)+('JAN-2013'!AE1)}, but this would not match the the employee & contract number. Moreover, as contracts become inactive and new contracts are added it would become a very manual process to update the formulas.

Can someone help me refine this formula?

 

[ChiChiR]

What version of Excel are you running? It seems like we could make a SUMIFS work. Would it be possible for you to paste in some of your data, including the cells you want included in the sum, into the thread, please?

 

[jojoluv377]

What version of Excel are you running? It seems like we could make a SUMIFS work. Would it be possible for you to paste in some of your data, including the cells you want included in the sum, into the thread, please?

I am using 2010.

I don't know how to post the data, but here is the what I would like to have happen

1. Feb-2013!Z2 = February hours (hard coded in)
2. Feb-2013!AA2 = Feb-2013! K2*Z2 (February hours*rate)
3. Feb-2013!AD2 =Jan-2013!AD2+Feb-2013!AA2+AB2-AC2 (January unbilled balance+February hours*rate+other direct cost-actual billed)
   1. In order for this to work, these criterias need to match: B2 (contract #),M2 (Last name) and N2 (First name). In oder words the formula should search in the previous month worksheet in column B for the contract, then column M for the last name and finally column N for the first name. Once is has matched these three criterias it would get the data on column AD cumpute columns AA+AB-AC (from the currect month) which would give me the cumulative unbilled.

 

[ChiChiR]

I apologize, I've not had a chance to take your information and lay it out in a spreadsheet to make the formula work. Here is what I would put in Feb-2013!AD2:

=SUMIFS('Jan-2013'!$AD:$AD,'Jan-2013'!$B:$B,'Feb-2013'!B2,'Jan-2013'!$M:$M,'Feb-2013'!M2,'Jan-2013'!$N:$N,'Feb-2013'!N2)+'Feb-2013'!AB2-'Feb-2013'!AC2

I THINK this will do what you want it to do. We can work through the mechanics of the SUMIFS if you'd like, but basically, the first parameter ('Jan-2013'!$AD:$AD) is the sum range, then you can put in as many criteria pairs as you'd like - range to evaluate,value ('Jan-2013'!$B:$B,'Feb-2013'!B2).

Hope this helps, and I apologize again for the delay.

Cece