Multiple Loan Repayment Optimization
I have 4 Loans of various interest rates, balances, and minimum payments.
Assuming I have a certain amount of money to pay out each month, how can I minimize the total amount I pay over the lifetime of the loans?
Given:
Total Monthly payment: M
Interest Rate for each loan: R1, R2, R3, R4
Initial Principal for each Loan: P1, P2, P3, P4
Minimum Monthly Payment: Min1, Min2, Min3, Min4
Each month, how should I distribute M over the 4 loans?
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Internal Repayment Formula
I have the following rows in my excel sheet: -Cash needed to start the quarter. -Net cash position. -Begining of the quarter debt owed. -Internal repayment. I am trying to develop a formula for internal repayment which must fulfill following requirements(Net cash position-cash needed to start the quarter),the remaining amount goes to internal repayment but that must be <=Begining of the quarter debt owed.
View Replies!
View Related
Optimization For An UDF
Optimize the code below to perform a search function for two variables. Right now it takes a while to execute as it goes through each cell row by row, column by column. It would be nice to optimize it to function a little faster (make that alot faster) . I realize my coding methods are not all best practice habits, so let me know your thoughts, workarounds or adjustments. In its basic form the code pasted in below is being used to locate data at the intersection of a given row and column. And this data can lie anywhere within this array, unsorted. Where the Row to search contains a team members name (Derek, John, etc.) D3:D50 and dates in columns Q3 to IU94. My spreadsheet has a data page with a list of team member names in D3 to D50, and a listing of dates running accross Q3 to IU94. Each members name may appear muliple times through out D3:D50. So for instance "Derek" (cell D6) worked 40 hours (cell Q6) the week of 9/1/08, it is the 40 that I want returned to a summary page and since the name Derek may show up more than once down the list I want to return all occurances where a date and name instersect on my summary sheet. [/quote] Below is my code which is currently used to lookup team member name "Lookup_Value1" for a given date "Lookup_Value2), it indexes the date column to search for data. Function LOOKUP2(Lookup_Value1 As Variant, Lookup_Value2 As Variant, TABLE_ARRAY As Range) Dim nRow As Long Dim nCol As Long nRow = 0 nCol = 0 With TABLE_ARRAY..............
View Replies!
View Related
Optimization Of The Find() Function
I use a lot the function “Find” in Excel but the problem is that it takes a lot of time, so I’m searching for another function or code that can be faster than that, the Worksheets that I use in Excel contain thousands of sheets so it takes hours to execute the Macro.
View Replies!
View Related
VBA Code Optimization
Can anyone recommend a good source for tips on optimizing VBA code? I'm working on an application that reads and interprets large text files (3500 lines) and starting to think about the efficiency of the code inside my read loop. For example I'm wondering how Excel actually implements access to cells internally. Is a cell just a variable or part of an array? If I do something like: range("A1").value = range("A1").value + 1 inside a loop is there a performance penalty compared with saying: count = count + 1 and then writing this to a cell outside the loop, eg: range("A1").value = count ?
View Replies!
View Related
Unexpected Time To Execute Optimization
Have an optimization question for you. I'm starting to try to optimize my macros and I've heard/read it's best to not activate or select anything. - I assumed that meant it would be more efficient to run code without it. I have a loop I run through about 600 times that takes .75 to .85 seconds to run through with the following piece:
View Replies!
View Related
Loan Balance
I owe 15462 in the bank, currency dont matter here, that is what I owe right now, but I want to have a cell in the frontpage with the amount left, so can I make a line called =remaining-each month the amount should then each month be substracted from the new month and so on, until the amount is 0 can this be done? the second page in the spreadsheet has a post with monthly pays to the bank ...
View Replies!
View Related
Loan Amortization Calculations
I'm working with a loan amortization worksheet (downloaded from Office Online). Unless I don't understand correctly, the date formula on the worksheet doesn't calculate the way I need it to. I'm not totally sure what the formula they use is doing. It does use a lot of named ranges. If a user inserts the total "Number of Payments per year", then I want the date to return the proper payment date. For instance, If the start date is 1/1/07 and the number of payments per year is 24 then the payment dates should be like 1/15/07 1/31/01 2/15/07 2/28/07 It should be the 15th and the last day of the month. If I put 52 as the number of payments then I want the formula to set the payment dates to every Friday. I'm still learning formulas so bare with me guys. Attached is the worksheet.
View Replies!
View Related
Calculate Home Loan Repayments
I have used the PMT function but this gives me the total to pay per month. I want to know what the repayments would be and also allow me to add in each one of the periods and extra repayment option. So if in the year I wanted to pay an extra $100 per month, I would put $100 next to each period as in a particular period(s) I might not have to put in there but want to predict what the amount owing on the house is. Is this possible or is this too complicated.
View Replies!
View Related
Is Loan Amortization Affected By The Currency
There are various references and links to " mortgage calculators;" though they are specific to the US dollar. Is the formula still the same, irrespective of the currency and why does it come across as quite a complex calculation? i have been taksed with designing a "calculator" and don't seem to know where to start as the currency issue is confusing me.
View Replies!
View Related
Effective Rate Calculation, Interest Only Loan
I have a calculation I do that calculates a clients "effective interest rate" if they make extra payments towards principal.. Calculation works fine.. However, I am now trying to figure out how to amend that code if it's an interest only loan, anyone have any ideas? Here is the effective rate calcs on a random normal amortization loan: this is in B2, and answer is 7% =RATE(B4*B5,-((B3+B7)/B6),B7)*12 B3 = Total*Interest 279017.8 B4 = #*Years*in*Loan 30 B5 = #*Payments*/*Year 12 B6 = Total*Payments 360 B7 = Beginning*Principal 200000 B8 - Ending*Balance 0 problem is when someone is on an interest only loan they pay more interest than a normal amortization because they are not reducing the principal in the first x number of years. So I need to compare the interest only effective rate to an interest only loan. Here is the example I'm working on... A client's loan is the following: Loan amount - 131,538 interest rate - 6.15 30 year amortization 10 years interest only normal client would pay an interest only payment of 674.13, then after i/o period would go to 953.80 for last 20 years of the loan, and they'd pay about $178k in interest.. Now if that client pays an extra 1,000 per year, I can calculate the amount of interest they'd accrue, but have no clue how to back into the "effective interest rate", basically that says you are paying the same amount of interest as someone with a x.xx% interest only loan.
View Replies!
View Related
Calculate The Monthly Repayments On A Loan Taken Out Over A Certain Amount Of Years
I need to calculate the monthly repayments on a loan taken out over a certain amount of years, which I can do fine. I just cant get my head around how to calculate monthly repayments over a certain amount of years when the intrest is compounding annualy. What I have so far: p*(1+(r/100))^n Where p is value of original loan, r is annual intrest rate, n is amount of years, and I am hoping I am right in saying this is the total repayable amount of the loan? Then putting that aside I created a amortization table. (which I am certain i forgot to include compound intrest in!) To keep it short i followed this guide for the amortization table. and now I am so confused about if I should be using PMT, PPMT, NPER?!
View Replies!
View Related
Macro Optimization And Moving Text From One Column To Another And Deleting Previous Column
I am running Win XP and Excel 2003. I have a macro I found here on the boards written by Lenze to delete an entire row based on what is found in column A. I would like to delete any row where Col. B contains 10 or less characters and I have modified it to do so (or at least I think it does). My problem is that it takes about 12 minutes to run the macro (I have about 50k lines to run through). I was wondering if this is the fastest method or if it examines things other than just column B. Sub Test() Dim i As Long LR = Cells(Rows.Count, "B").End(xlUp).Row For i = LR To 2 Step -1 If Len(Cells(i, "B")) < 11 Then Cells(i, "B").EntireRow.Delete Next i End Sub After this runs, I am left with Columns A to somewhere around AH. The columns are generally in the format of text followed by a numeric column. An individual text column has the same name through all of the rows. The numeric columns have varied values whether negative or positive. Ideally what I would like: If a given cell (ie. C2) in Row 2 is numeric, then copy the cell to the left (ie. B2) into (ie. C1) and then delete Column B. I need this to work for multiple columns from B to C, skip D and E, and then from F to AG (and maybe beyond).
View Replies!
View Related
Loan Processing-Copy Range From 1 Sheet To Same Range On Another
I'm developing a loan processing system for members of a club. When an applicant asks for a loan, the club will calculate 10 % of that interest and the applicant will have to pay it back in 5 successive fortnightly instalments. If he asks for a loan in the first fortnight (1), for example, he will have to start paying instalments in fortnights 2,3,4,5,6 to pay it all back. The system currently has 4 worksheets. The first sheet is a the loan application form. The cells outlined in thicker border, are the cells in which details must be input. Once it is input, the data will be automatically placed in the Processing worksheet using IF and VLookup functions (See spreadsheet attached), which is used as a basis for the loan schedule Worksheet. What I need is a macro that will copy the range filled in the Processing worksheet, and copy it to the exact same location in the Loan schedule worksheet (The cells with the same fortnight columns and the same member name. This is how the loans are to be filed.
View Replies!
View Related
Combining Multiple Cells In Multiple Worksheets In Multiple Workbooks Into One Table
I'm currently doing a survey using an excel workbook that contains multiple questions across multiple worksheets using radio buttons linked to certain cells. I have around 400 workbooks coming back to me, so what i want to do is take specific values from across many worksheets within each workbook and combine them into a large master table in a seperate workbook. I've tried using VBA, but not being very proficient at it i've hit a brick wall with that, so i'm hoping that there is an easier way to do it than what i'm currently pursuing.
View Replies!
View Related
Copy Multiple Columns From Multiple Excel Files & Paste Into 1 Workbook
I have 8 different files all have a set of data in them each one has a long list of (column a-n) however the number of rows change by date. I need each file copied into the finalfile.xls one after another. in the files that will be merged into the final file the final row i need copied is blank. I have all the copy formulas and everything set, I just need a range to copy that automaticaly takes cell a10 to the first blank a cell from each file and pastes it in the finalfile.xls under the last paste so they dont over write each other.
View Replies!
View Related
Combine Workbooks With Multiple Sheets Into 1 Multiple Sheet Workbook
I have about 20 workbooks with different file names for different projects all saved in the same folder. Each workbook has about 10 worksheets and each worksheet is named in a similar fashion in each of the 20 workbooks (eg. revenue, cost, variance etc.). I want to pull out a worksheet named ' forecast' from each workbook into a master workbook so that the master workbook would contain the 20 forecast worksheets.
View Replies!
View Related
Multiple UserForms With Multiple Numeric Data Input TextBoxes
When one creates multiple UserForms with multiple (identical) TextBoxes, every control must have its own event handler procedures. All these TextBoxes in my workbook are to capture numeric data to populate various cells in the workbook. Would you recommend using a Class Module to handle these events for TextBox controls, rather than having to repeat the event handler code for each control? And if so, do you have some code that I can use that will cover most of the events and potential error handling routines for numeric input data?
View Replies!
View Related
Copy Rows Meeting Multiple Criteria On Multiple Columns
1. Copy data from original file (I do not want to do anything in the original file) into the spreadsheet (Target worksheet)where the code should run. 2. In sheet 1 of Target Worksheet, there are 2 columns which I need to set criterias on namely Column D and Column L 3. In Column D, I want to specify 3 criterias namely A, B and C 4. In Column L, I want to specify 5 criterias namely London, Frankfurt, New York, Sydney and Tokyo 5. If Criterias in 3 and 4 are met, copy all rows into Sheet 2 of Target Worksheet
View Replies!
View Related
Copy Multiple Values From Multiple Workbooks To Master Workbook
I want create a macro which will extract 6 values (see below) from a workbook tab called summary to an master workbook for reporting purposes. Each workbook has a unique file name e,g ACI1150. Values on sheet SUMMARY: A1 A2 A3 G21 G24 G26 I tried to adapt the below to get one item copied/extracted. However it would no work. I am new to using macros Sub GetG26s() Dim MyDir As String, FN As String, SN As String, NR As Long Application.ScreenUpdating = False
View Replies!
View Related
Count Unique Logs With Multiple Conditions Of Multiple Sheets
I've got no clue about all this, but I've had to get specific formula examples and fill in the blanks in order for my timesheet to work. There's just one final problem if somebody could please help. This is a timesheet for a 5 day work week. I need to count the number of unique log numbers for a specific activity. The log numbers counted must be unique across the entire week, not just for each day, which means I want the formula to count the unique log numbers across multiple sheets. The formula also has multiple conditions. I got 2 columns. The first part of the formula needs to verify a word, say, "split" and if it does it checks the adjacent cell for a unique log number. If both arguments are true, it counts the log as 1 unit. Here is a working formula for only one page. =COUNT(IF(D4:D29="split",IF(FREQUENCY(C4:C28,C4:C28)>0,1,))) Here's 2 problems with this formula: 1. I will count if it encounters a blank cell in the Log numbers the first time (which will happen as not every activity we do has a log#), but it will stop counting if it encounters a second blank cell. 2. I don't know how to make it work across several sheets. This is an alternate formula which works and skips the blank cells, but I don't know how to add the multiple condition of "split" and to have it work across multiple sheets. I just copied it Microsoft. As I said, I don't understand it, I just fill in the blanks. SUM(IF(FREQUENCY(IF(LEN(C4:C29)>0,MATCH(C4:C29,C4:C29,0),""), IF(LEN(C4:C29)>0,MATCH(C4:C29,C4:C29,0),""))>0,1))
View Replies!
View Related
Paste Multiple Cell Values From Multiple Workooks Into One Workbook
I’ve got a folder on drive C named “Customer Sheets”. In this folder at any time there could be over 500 individual customer sheets. i also have a "Summary Workbook" on drive C What I need is a macro in my "Summary Workbook" that copies cells (“B3, B6, B12, B13, G5, G6”) form every customer sheet in my “Customers Sheets” folder and paste it into my “Summary Workbook” onto sheet2, every customer in a new row. The “Summary Workbook” should run this macro every time it is opened or on my command so that the database is continually refreshed when a new customer sheet is made.
View Replies!
View Related
Count Rows Meeting Multiple Criteria Of Multiple Values
I am trying to find a formula to count rows that meet multiple criteria, but one of the criteria can be multiple values. I have a list of people with a list of clients that they are responsible for. Each person is responsible for 10-20 clients. Every day I run a report that shows the project worksheets submitted for each client and if money has been awarded or not. I'm wondering if there is a way to count, for each person, the number of project worksheets that show "awarded" in column K. That would mean that I would have to look for, for each person, any of their multiple clients in column B and "awarded" in column K. I am trying to put the formula in D2:D9, as I use A2:D9 for a chart. O1:P79 contain the names of the people and the applicants that they are responsible for. A17:D158 contains the list of project worksheets (updated daily). I used =SUMPRODUCT(COUNTIF(B17:B999,P1:P14)) to count the actual number of project worksheets for each person, but I can't figure out a way to modify that to add in the "awarded" criteria also.
View Replies!
View Related
Adding Multiple Cells From Multiple Sheets With Sumif Function
I'm trying to put together a spreadsheet that tracks disc capacity increases, affected by any incoming projects. I've managed to do so for one project, but would like to for up to 10. The way i've designed the solution (i'm sure there are far more elegant ways, but hey) is thus: A forecast worksheet keeps track of a grand total, taking information from sheets P1 -> P10 (being projects 1 to 10). I am unable to figure a way to add up all the increases from all 10 project worksheets with one succinct formula. What I use so far is: ='P1'!C83+SUMIF('P1'!E82,"=2009 - Q1",'P1'!D82) ..................
View Replies!
View Related
Match Value Of 1 Cell To Multiple Cells And Give Multiple Values
Sheet 2 is the problem. I need to match cells e3:h3 whenver the dates change in in cells c5:c9 and then put there result of cells b5:b9 into the corresponding cells of e5:h9. Basically what I'm saying is that I want to keep a track of all previous pay amounts from each pay period. So when the next period changes the date it also copies the new pay amount to the corresponding date of the previous pay section. I have included a sample.
View Replies!
View Related
Using 2 Variables To Return Multiple Items From Multiple Sheets
I have a need to populate a summary worksheet using two variables to find data in two or more other worksheets. I find writing out what I want helps some times so let me try it here. So my variables are: Product (there are 22 products) Supply Less than (inset number) These are the two criteria I want to use to produce a result. The next issue is I have 300 stores that carry said 22 products. Each store has a unique number 0001, 0002, 0003 etc. So in a separate worksheet I have a list of the store numbers, and then the products. So each product has the store's number to the left in Column A, Column B has the product name, Column C has the quantity on hand. What i would like to do on the summary page is select the product, and then select the supply less than or equal to 'x' and then have the stores with the selected product less than or equal to x display below. The last part of this is then to display (data from an other sheet) on the summary page which contains the quantity of the product selected available at the warehouse for that store.
View Replies!
View Related
Programming Multiple Inputs With Multiple Data Fields
I am trying to set up a "diet log" for my nephew to track his eating habits for a school project. I am trying to make the "total" section of the attached spread sheet auto calculating depending on the information placed in the "breakfast / lunch / dinner / snack" catagories. I have two worksheets in the file. The first being the preferred method of a single cell and seperating the items using only a ",". The second sheet has a little bit different lay out which I don't think will work as nicely. Honestly, I am not sure if this will even be possible, but I figured I would try here as a final resort. Currently there are just some sample foods in there as I will let him fill in the actual workbook for his use.
View Replies!
View Related
Copying VB To Multiple Sheets In Multiple Work Books
Ive put some sheet code together that i need copied to 12 sheets (jan to December) in 24 workbooks (each workbook has trhe same sheet names). I dont want to alter the actual content of the Excel sheets, I just need to copy VB code from a template (in VB editor) to the 12 sheets in each of the workbooks. Is this possible to do with VB or do i need some other utility since Im using the VB editor....
View Replies!
View Related
Lookup Multiple Same Value And Return Multiple Corresponding Value In Ascending Order
I have a problem with the formula that look up multiple records with the same values and return multiple corresponding values in ascending order. I am using Excel 2003 and it is a bit complicated to explain so I have attached a sample spreadsheet to show what I mean. What I want was after I have sorted the occurrence value in column E based on column B and I want to correspond the Rank in column D based on column A in ascending order for the same occurrence value in column E. Eg: There is two occurrences for number 1 at E3 and E4, and three occurrences for number 2 at E5, E6 and E7 in column E. Then the Rank for the first occurrence for number 1 in D3 should be ranking 6 and the second occurrence for number 1 in D4 should be ranking 7, so the Rank for the first occurrence for number 2 in D5 should be ranking 3, D6 should be ranking 4 and D7 should be ranking 9 based on column A and B, etc.
View Replies!
View Related
IF Statement Using Multiple Variables To Give Multiple Outcomes
I'm looking to work out an IF statement based on a series of dates entered to give 1 of 3 possable outcomes. Where: A1: todays date (exmaple =NOW()) B1: due date (exmaple 10/02/09) C1: completion date (example 12/02/09) D1: status (overdue, outstanding or completed) D1: =IF(A1>B1,"overdue",IF(A1<B1,"outstanding",IF(C1<=>A1 & B1,"completed"))) I know the last part is totally wrong (symbols arranged in that manner), but to clarify i would like the date entered in C1 to override the other statements in the fomula to make it read "completed". If no date is entered in C1 then the formula will return either "overdue" or "outstanding" depending on the other dates in A1 and B1.
View Replies!
View Related
Print Multiple Ranges From Multiple Sheets Userform
I inherited a spreadsheet that had an userform where the user checked off which 'pages' he wanted to print. The Ok button routine used if statements to run a routine for each 'page.' Here's an example of the original code for one page: Sub Button2_Click() Sheet7.Activate Run "HorizontalPrintStuff" 'generic landscape pagesetup With ActiveSheet.PageSetup 'specific pageset settings .RightFooter = " Construction Assumptions" .PrintArea = "CONSTRUCTION" 'the named range to print .Zoom = False .FitToPagesTall = 1 .FitToPagesWide = 1 'this changes depending upon the page selected End With ActiveSheet.PrintOut End Sub The problem was it printed each page as a separate print job; and if you print to adobe, you get serveral files, not one file. That and it took a long time to run. So I tried a different tack. If the checkboxes has true, then the printarea is set to that named range. If there were more than one named range on a sheet to be printed, I consolidated them. I did this with a bunch of if statements - very cumbersome. Sheet3.Activate 'Sheet3.ResetAllPageBreaks 'disabled due to errors Run "HorizontalPrintStuff" 'generic landscape pagesetup With ActiveSheet.PageSetup 'specific pageset settings .PrintArea = "DEVBGTALL" 'the named range to print .FitToPagesWide = 4 'this changes depending upon the .FitToPagesTall = 1 End With I haven't shown all the code cause it goes on for 12 sheets containing 16 different printareas. My current muck ups are ..... 1) it prints every printarea/named range on a given sheet (I took out all the if statements trying to debug everything.) Is there another conditional argument that allows for multiple 'trues'? 2) the pagebreaks in printarea/named ranges that are multiple pages (like a 48 month schedule) won't stay set. I've tried both VPageBreaks(3).Location:= and .VPageBreaks.Add Before:= 3) the Sheet1.select false argument is always adding a random sheet to the end of the print job. Don't know why. I can do all this in a recorded macro, just not the selection userform. I've thought about copying to another sheet or hiding columns and rows then printing, but that seems just as cumbersome. To recap, i want to print out, as one print job, multiple printareas from mulitple sheets, based upon checkbox selection on an userform.
View Replies!
View Related
Multiple Parameter Lookup For Multiple Table Ranges
In the attached file i have multiple tables for different types of conservatory roofs (16 of them in total). The ranges at the top and side relate to milimeter measurements and the data in the middle relate to the price for that sized conservatory roof. The table works where the two ranges intercept each other. I have a formula to do this for one of the tables only. What i would like is a way of choosing which type of roof to use (i.e. which table to use) and then to be able to input the measurements and the price to be displayed. All of this needs to be done in one query so its as user friendly as possible. i've had is to use a pivot table, i feel it is not possible to use a pivot table to do this sort if thing after research into them, although i am un-familiar in the making of them
View Replies!
View Related
Create Multiple Files For Multiple Groups Of Data
I have one main Excel file with information (in example file - columns A & B). I want to create 4 other Excel files that will draw from the main one (in example file - columns D, F, H, & J). I want the information in Excel file A, B, C, and D to be continuous information that matches the main file. For example, the letter A matches up with numbers 1,3,4,10,11,13,15,17, and 19 in the main file. When I open Excel file A, I want a continuous list of these numbers. And I want this for all 4 Excel files which draw from the main one.
View Replies!
View Related
Combine Multiple Sheets From Multiple Books Into One
I have 6 spreadsheets all within the same folder, these are pretty much identical (rows, colums, sheets within them) apart from the names of the files. I then have a master spreadsheet within the same folder where I want to combine all the data, from all the sheets within each book (if that makes sense!) apart from the data on the last sheet within each book as this is the reference data, onto one sheet within this master file. If possible I only want to copy rows accross which have complete data too. So: (names not correct) From book1.xls copy all data on sheets (sheet1, sheet2 etc) except last sheet From book2.xls copy all data on sheets (sheet1, sheet2 etc) except last sheet combine onto masterfile.xls on sheet1. I have searched on here and can only find how to do it with the first sheet in each workbook, not looping through all the sheets in each book. Please see below.
View Replies!
View Related
How To Satisfy Multiple Conditions Using Multiple Criteria
I'm trying to divide my data into 6 different groups, based on 2 different criteria. First, I am not sure how to write the logical test to take 2 columns of data into consideration (using "&" and "AND" do not work; I am not sure what else to try), and second I can't figure out how to write the formula so that it can select from 1 of 6 conditions. So, overall here is what I want: If DL2=3 and CK2=1, then I want this to be labeled as '1' If DL2=3 and CK2=2, 2 If DL2=1 and CK2=2, 3 If DL2=2 and CK2=2, 4 If DL2=1 and CK2=1, 5 If DL2=2 and CK2=1, 6 These 6 conditions cover all possible combinations of numbers in the two columns.
View Replies!
View Related
Multiple Column Lookup Across Multiple Sheets
After going through multiple threads in the forums, I got this code to do a multiple VLOOKUP method. =IF(E2+F2=0,"",INDEX(C2:C10,MATCH(1,(A2:A10=E2)*(B2:B10=F2),0))) It works perfect on a sample sheet. But when im trying to implement it in a sheet with too much data, it always fails. I have attached the sheet I am trying the formula on. I have grayed down the columns which needs formula's. The data is picked out from the second sheet. This is how I have modified the formula to suit me.. =INDEX(Data!$G$2:$G$663,MATCH(1,(Data!$A$2:$A$663=$C2)*(LEFT(TEXT(Data!$E$2:$E$663,"mm/dd/yy hh:mm:ss"),8)=$I2),0)) <<Please note that all the dates and numbers in the sheet are in "text" format for ease of use>>
View Replies!
View Related
Sum Across Multiple Tabs, Multiple Criteria
Excel 2007 My workbook contains 13 tabs - 1,2,3,...12, and Summary My data starts on line 4 of every sheet but varies in length - so far the longest goes to line 30. Rows used on all 13 sheet are as follows: A - contains facility names B - contains a two or three letter code C - contains hours D - contains dollars E - contains adjusted rate On the Summary tab I have listed all the facilites and two or three letter codes. I need to sum column "C" on tabs 1-12 when they match columns A & B on the summary tab. I have tried the following but can't get them to work: =IF($A5=""," ",SUMPRODUCT(--('1:[12]12'!A$4:$A$50=$A5),--('1:[12]12'!B$4:$B$50=$B5),'1:12'!D$4:$D$50)) I did not put the [12] excel added that automatically I had 1:12 =SUMPRODUCT(--(THREED('1:12'!$A$4:$A$50)=A10)*(THREED('1:12'!$B$4:$B$50)=B10),(THREED('1:12'!C4:C50))) I just seen the THREED for the first time today and am not sure if this was the correct place to try but it didn't work anyway
View Replies!
View Related
Sum Multiple Product For Multiple Values
is it possible to use the sum product formula to give me the total results for multiple values? I know I can ask it to look at column a for a value of e.g "44" and column B a value of "gin2334" but can I ask it to ge me total for values from column A that equal "44, 45, 46, 47" ans B "gin2334"? if not has any got a clue as to how i would get these results?
View Replies!
View Related
|
TRACKER
