Rolling total

jojoluv377

New Member
Joined
Sep 4, 2009
Messages
10
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?
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
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?
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,562
Messages
6,114,322
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top