I am trying to match 3 values, and return a matching 4th from a chart. It will first look at one value and see if it falls between values (the LOAD value in cell B1..it will look to see if it falls between the "low limit" and "high limit" in columns A10 to A135 and C10 to C135, respectively. Then it will look for a match for the values in cell A2, and in cell A3. the return value will index to the corresponding value in column G10 to G135. I have written a formula to do this in cell D2....but it will only ever return the value in cell G10.
I am looking for a bit of assistance with the following formula. What I am trying to do is use sumproduct to sum on 2 variables. I have been trying to make this happen all day and if there is already a post on here to make it work I can't find it.
The problem revolves around the middle section. I am looking to match items in a text string (it is a mixture of text and number but are all as text)
I am looking to match a product that beings 200 (which might be 2000123) I don't want to match something like G200TRF I have tried every combination of left, right, find and search but I still have the same issue.
I would like to write a formula that I believe will be a SUMPRODUCT formula with a very complicated string of MATCH and INDEX (I think). I hope this can be written in one formula - but if I need a couple to get there, that would be okay too. I am modifying an existing workbook that currently does the calculation, but it need a separate sheet for each payment - up to 12, but it can't be easily modified for more, and I need to do about 120. I can do limited SUMPRODUCT formulas and can do some limited MATCH formulas, but I can't begin to figure out how I would string this formula together. Hopefully what I need to do is clear with the excel shot below. If not, I could post a sheet that does the calculation for one payment. Thank you for working any magic you can on this.
I will try to explain how this formula needs to work. The formula needs to calculate interest on a payment, from one date to another date using a set of fixed interest rates that are established for each quarter. So this is what the formula needs to do. The workbook has a table (see below) that identifies the quarter - dates from and to, the number of days in the quarter (may be needed in the formula, but maybe the formula would calcualte?) The first and last quarter would most likely not be applied for the full number of days, since the payment would be after the start of a quarter, and the end date may not be at the end of the quarter. It needs to determine if the payment falls in a quarter, how many days then are in that quarter, and apply the base dollar amount for the number of days at the daily interest rate. So it would need to look at all dates and do this calculation and add each of the quarters together until it gets to the "Calculate to date" (cell J5). In the sheet below, I would put the formulas for each paymant in cells K8 to K14.
******** ******************** ************************************************************************>Microsoft Excel - Test.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutD5F5D6F6D7F7D8F8D10F10D11F11D12F12D13F13D15F15D16F16D17F17D18F18D20F20D21F21D22F22D23F23D25F25D26F26D27F27D28F28=BCDEFGHIJK4QUARTER DAYS/QTRRATESDAILY RATE 51/1/20013/31/2001909.0%0.00024658 Calculate interest to: 3/31/2009 64/1/20016/30/2001918.0%0.00021918 PAYMENTDATES INTEREST BASE INTEREST77/1/20019/30/2001927.0%0.00019178 810/1/200112/31/2001927.0%0.00019178 112/2/2001 $ 2,424 9 21/11/2002 2,486 101/1/20023/31/2002906.0%0.00016438 32/17/2002 21,011 114/1/20026/30/2002916.0%0.00016438 43/19/2002 3,827 127/1/20029/30/2002926.0%0.00016438 54/22/2002 54,971 1310/1/200212/31/2002926.0%0.00016438 65/25/2002 1,255 14 76/24/2002 4,883 151/1/20033/31/2003905.0%0.00013699 164/1/20036/30/2003915.0%0.00013699 177/1/20039/30/2003925.0%0.00013699 1810/1/200312/31/2003924.0%0.00010959 19 201/1/20043/31/2004914.0%0.00010929 214/1/20046/30/2004915.0%0.00013661 227/1/20049/30/2004924.0%0.00010929 2310/1/200412/31/2004925.0%0.00013661 24 251/1/20053/31/2005905.0%0.00013699 264/1/20056/30/2005916.0%0.00016438 277/1/20059/30/2005926.0%0.00016438 2810/1/200512/31/2005927.0%0.00019178 Sheet1 [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
See attached spreadsheet. I have 2 tabs. The current portfolio tab is feeding in from the computation tab. My formula in column C works for 2 securities, MSFT, and AApl as the name is identical in both tabs. I am running into an issue where the names are not identical.
Example: I want to sum both the INTC, and the INTC pref in the computation tab and have that fill in the current portfolio tab. Also, in the computation tab ticker 005930 ks should fill in the current portolio tab for samsung electronics. My lasst issue is in the computation tab where I want to pull in sony, and sony adr (multiplied by 1,500). total sum 1,5723,995,015 in the current portfolio for sony.
How to use the Sumproduct with index and match formula and then multipling by a set percentage.
I want to take all amounts in K10:K113 and have them add together based on specific criteria in A1:A113 and in B1:B113 and then multiply the result by a set percentage.
I'm using a formula (with the SUMPRODUCT function) within which I need to match the month & year from an array with 3 "true" possibilities...
Meaning that if data (month & year) from the array matches any of the 3 months & year of a specific quarter (for example July 08, August 08, September 08), the result of that section should be "True".
The data to be matched (any of the 3 months of each quarter) is drawn from a table header (showing every month of the year) by using this "monthly" formula:
TEXT(H7;"mm.yyyy") where H7 holds "01.07.2008" (but displays "Jul. 08") and returns "07.2008".
I'm trying to extend it to something like:
TEXT({H$7;I$7;J$7};"mm.yyyy") to match either July 2008 (H7), August 2008 (I7) or September 2008 (J7) for the 3rd quarter of 2008 but when I hit Enter, I get an error and H$7 is highlighted ("ctrl + shift + enter" doesn't help).
Here's an excerpt of the formula that works well for other queries on my worksheet:
Here is what I have. 4 Worksheets. The first worksheet is a summary page. I have 350 personnel that are broken down into three different groups. So each group has it's own sheet. Here is what I need to accomplish. Results need to be posted on the summary sheet.
I need to compare cells B2 & D3 for each row on a worksheet and display the number of times they match on a worksheet. For example how many times does EP & EP match on a certain row. I need to compare cells B2 & D3 for each row on a worksheeet and display the number of times they don't match on a worksheet. For example how many times does EP & MP occur. I've attached an example for reference
Is there a way to provide filter with a list of criteria but when it doesnt match all of the criteria it still uses the filter on the criteria that it does match?
However sometimes for example B will be missing, or H or B H I will be missing etc... is there a way to provide all of the criteria and it will not error if the criteria is not all there?
I should be able to do this by now but I keep getting an #NA error. I am trying to use this sumproduct formula =SUMPRODUCT(($I$7:$I$2585=921),--($L$7:$L$2585="Fb")--(AS7:AS2585)) where I am looking up store #"921" and Attribute "fb and sum where these two criteria meet. The column I want to sum is AS7:As285. What am I doing wrong.
In Column A1:A20, these are included 5 types of data (red, orange, yellow, green, blue); in column B1:B20, these are included the total salary amount, and in column C1:C:20, these are included "Sole" , "Joint" or "Partnership", so if column A is included red/yellow/green (excluding orange and blue) and column C is included Sole/joint/Partenship, and total salary is between 6000 and 9000, then calculate the total salary of all the customers. How can I use sumproduct function to fix it?
Now I'm trying to improve the functionality of the file attached above.I've attached the file again with another drop-down menu. What I'm trying to do is to have a sum of all the months between the two months indicated in the drop down menu.For example by picking two months from the drop down menus, I want to know the sum of the values between Jan-Mar (Jan, Feb and Mar), or for example Jul-Oct (Jul,Aug,Sep and Oct)...
I've tried to combine SUMPRODUCT with INDEX and MATCH but it doesn't work. I want to build upon this formula:
[Code]....
I forgot to mention that I have multiple strings (column E) which are NOT unique. Basically "VLOOKUP way" won't work, rather it should be "SUMIF way".
In the attached file I've added one additional row (r19), which is the same as r18, just to try how the summing will work.
I am looking around any way wherein I can sumproduct the values as given in attached sheet, basically I wanted to know the total MRP value of Sale and Stock
I'm working on a spreadsheet at work and I'm trying to sum only the values in a column that meet criteria in the column next to it. For example, in the attached spreadsheet, I would like to have a totals line at the bottom of the spreadsheet for all three Facilities and the total next to them. I know I could do a pivot table but I know this approach would be more aesthetic and easier for my supervisor.
I am stuck - I have a large amount of data for a group of physicians I work for. I am trying to set up a monthly trend report to be able to run quickly after I plug in the data. I want to use some sort of lookup to look up two things - 1) the physician's specialty and 2) the month.
Can anyone look at the attached example and tell me how to do this? I have started a SUMPRODUCT formula, but am stuck on how to tell it to find only that month's data.
I have the following sumproduct formula that's providing solid results but I would like to add a 3rd column of criteria. I'v tired with little succes.
The following formula <=SUMPRODUCT(('IW 38 DUMP for Planning'!$A$1:$A$10000="2A")*('IW 38 DUMP for Planning'!$E1:$E10000={"PAA","RS","RSNR","S","SAM","SAMT","SAO","SAT","SOR","WKS"}))> totals all of the work in plant area "2A", in this case 52 records. I would like it to filter further with values in $H1:$H1000 matching criteria "CONTRACT", "MACH" OR "HTSMET".
The data is easy to find with pivot tables but I would like to take that manual step out of the reporting being doen from these records.
I was wondering if I could use a range of cells as my criteria as opposed to inserting quotation marks with each criteria. For example in the syntax below can I do something like this? Report!$C$3:$C$5000=B45:B51?
I have the following sumproduct formula which looks in one column to search for a series of dates (greater than one date and less than another date). It then looks in column O to see if there are certain criteria if the date criteria is met. The problem is I have a lot of blank cells in column O which need to be captured.
I'm trying to create a budget worksheet that pulls actual data from another sheet within the file for comparison (Budget vs. Actual). There are two criteria: 1) the actual transaction falls into the same category of transaction as the budget line item (e.g., mortgage payment) and 2) the date of the actual transaction matches the month in the budget (e.g., a January or March transaction isn't pulled into the actual data for February budget information). From there, I'd like it to sum any charges or reduce by any deposits for those given criteria.
I've tried numerous things from DSUM, to SUMIF with IF, to SUMPRODUCT.
I received an answer to my original question and now have a new question but I wanted to reference my original for the history. I posted my new question at the end of my original thread.
I have spent the last 2 1/2 hours trying to figure this one out on my own. I have attempted multiple possiblities from the forum, however without success.
Okay, I need a count based on 2 criteria. My data source is a different workbook.
My last attempt on a formula is:
=SUMPRODUCT(('[eTE Status - TX HSP Math 2009.xls]Project'!$B$2:$B$248=D2)*('[eTE Status - TX HSP Math 2009.xls]Project'!$A$2:$A$248="978*"))
Column A - ISBN # This will ALWAYS begin with 978 or will contain N/A. There are no blank cells in this column Column B - Grade
In the formula above, cell D2 represents the Grade criteria. And in the second part of the criteria I was trying to create a wildcard so all numbers beginning with 978. Excel "accepts" this formula with giving me an error message, but yields 0 as a result rather than 34.
SUMIF allows one to enter a [valid] criteria text string into cell D2, say ">14000", and then reference D2 as the function criteria, e.g., =SUMIF(B5:B10,D2,C5:C10).
Is there a syntax that SUMPRODUCT will accept that does the same thing? Note the inclusion of a comparison operator is important.
I have a sumproduct formula based on some criteria, but I don't know how add another criteria wherein I need to exclude in the count if the date in column F is 1/1/2009
