Combine SUMPRODUCT With INDEX And MATCH But It Doesn't Work?
Dec 15, 2013
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.
making a roster file for our school clinic so that reports can be generated automatically and there are less hand-written records around.
I am having trouble with the 'Supplies' sheet, where I am attempting to sum up the quantities (column k) adjacent to all occurrences of a supply in Column J sheets 1 through 31. So that we can know how much of x supply was used in the whole month and so on.
=INDEX('24'!$J$3:$K$106,MATCH(B5,'24'!$J$3:$J$106,FALSE),2) Returns a value, but that is because sheet 24 has a value for b5, problem is if i use +INDEX and any of the sheets dont have a value for B5, the result is either VALUE or N/A =SUMIF('1:31'!J5:J105,B6,'1:31'!K5:K105) gives a VALUE! error
I've also tried =INDEX('1:31'!$J$3:$K$106,MATCH(B5,'1:31'!$J$3:$J$106,FALSE),2)
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.
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 have 3 calculations I would like to make based on data in the spread sheet and I can't seem to get them to work with data from the two separate columns.
I tried a few of the index match max formulas I found here and could only get them to work with one column of data.
I have the spread sheet attached and the 3 calks I want to do are blank on the bottom.
I have an array formula: {=SUM(IF(MATCH(Group,Group,0)=(ROW(Group)-ROW($C2)+1),1,0))}
where Group is: OFFSET(Groups!$C$2,0,0,Groups!$D$1)
and the array formula is entered in Groups!E1 and returns an #N/A error.
If I select the formula in the Formula bar and press F9 it evaluates to 48 which is the result I am looking for.
If it calculates OK using F9, why can't excel resolve it in the cell??
When I enter the match part as an array {=MATCH(Group,Group,0)} in an appropriately sized range, it works fine and returns the index of the first occurrence of the elements of the Groups array as it should.
The Groups array in column C is an array formula entered over 388 rows and contains: {=IF($A2:$A389="","",INDEX(XCorel!$IC$2:$IC$232,$A2:$A389))} where XCorel is another sheet in the same workbook.
Can anyone suggest why this might be? If I replace the array formula in column C (Group) with its values the problem goes away, but I want it to be dynamic.
I have a hyperlink to [url] that doesn't work. When I click it I get an error saying "Unable to locate the server or proxy server". But when I cut & paste the address from the hyperlink into my browser (no chance of mistyping), it works fine.
As said, the right click doesn't work anymore, last week it was working but it doesn't anymore. When I right-click wherever in the spreadsheet nothing happens but I know it's working because it works outside excel.
I need to delete all the digits before / and also the / - in the valuta columns. But this =RIGHT(B2,LEN(B2)-6) doesnt work It says > the formula you typed contains an error
What am I doing wrong here. Some times if I do a =if( ) formula it doesn't work. For example, look at the attached picture. Cell K63 should say "End of Run". But it doesn't, What gives?
Also I have had before where i do =IF(X62>$O$2,"End of Run", 0) (and $O$2 =81) and the cell when X62 is 81, not greater than. I know I can to >=, but thats not what I am doing.
i got a long macro to delete all rows with contains a column with the value 'xx'. The macro works in sheet 2K, 2F and 1G. But the weird thing is it doesnt work in sheet 1K (i copied the exact same part of the macro out of the other sheets). Is it possible that there are to many rows in the sheet (sheet 1K has 24.000 rows, 50% or more will be deleted with the macro. The error says bug in the line: If Not rng Is Nothing Then rng.EntireRow.Delete It stops showing all the rows wich should be deleted.
Sub GrondeigenarenVerwijderen() Application.Goto ActiveWorkbook.Sheets("2K").Cells(1, 1) Dim DeleteValue As String
I have below macro for Sheet2 and i have created a button on Sheet1.now whenever i press this button the macro will run and save Sheet2 as PDF file.
The problem is when i hide Sheet2 the macro doesnt work and it gives an error " Invalid procedure call or argument.
how to make this macro run even when the sheets are hidden ?
Sub PDF_Table() 'Sheet2.PageSetup.PrintArea = "$a$1:$x$140" '***** '*** can remove the above line if sheet areas are already set and will not be altered With Sheet2.PageSetup .CenterHorizontally = True .CenterVertically = True .Orientation = xlPortrait .Zoom = 60
I want to sum the NUMBERS in golumn G if the DATE in column L matches the DATE in cell F37 AND if the TEXT value in column B is equal to the TEXT value in cell G36.
The answer is probably "because I'm stupid", but I really can't get my head around it! I'm playing with variant arrays for the first (and possibly last) time,
The code I have is:
Sub test() Dim vSheetColours As Variant Dim iCounter As Integer vSheetColours = Range("Colours").Interior.ColorIndex For iCounter = 1 To UBound(vSheetColours, 1) MsgBox vSheetColours(iCounter, 1) Next iCounter End Sub
(Obviously this code doesn't do anything useful - but if I could get it to work, I might have a chance of making my real code work!)
Colours is a range of 8 cells. Each one has some text in, and has a different background colour. I'm trying to store the colours.
If I run this code, I get a runtime error 13 type mismatch, and it highlights For iCounter = 1 To UBound(vSheetColours, 1)
But if I replace vSheetColours = Range("Colours").Interior.ColorIndex with vSheetColours = Range("Colours").Value
This might be extremely easy, but why my Excel (nor Word for that matter) doesn't go to full screen anymore? I'm pretty sure that I haven't used any options, I do have a new graphics card, but all the other applications goes to full screen. I have restarted my computer. Basically I think that it's either some option, or then reinstalling Office.
I have a programme which works fine on my computer at home but when I send it via email to work the 'sort' function messes up (there is a chance it could be the 'vlookup' function, though) and all the cells in the sort range lose their formula reference - which is of course a tad annoying.
The part of the programme I'm having difficulty with is this:
I have a workbook that I transfer back and forth from my desktop to my laptop, that uses Sendkeys to add code to the active pane in VBA. It doesn't work on my laptop, but works fine on my desktop.
Both computers have Vista. Is there a switch somewhere to make Sendkeys work?
I'm not sure why this macro in workbook "A" is not working when checking for the activesheet sheet opened in workbook "B"? I've included the tab name I'm looking for and the tab name opened in the workbook "B" in the MsgBox to verify it can pull the tab names properly in workbook "B" and it returns correctly. I'm not sure why the If Then Else is not working if that is the issue here.
Code: Sub testmonthcheck() Dim art156 As Workbook Dim mm As String Dim mmm As String Set art156 = Workbooks("Actual_Run_Times_156.xls")
HTML Code: If Left(Range("H18"), 7) = A23C567 Or Left(Range("H18"), 7) = A65C321 Then ActiveSheet.Cells(6, 7).Value = "Business and Private Banking" End If
Im trying top use the above bit of code and it does not work when the criteria is Text , so if I simply change the above to =1234567 it works fine -- But I need it to be alpha numeric
where the first term refers to cell immediately above. In other words, this formula takes the prior balance, adds a credit if any, subtracts a debit, if any, and gives a new balance.
The formula worked on the cell I put it in. It will not work when I copy and pasted it into the cell below the first one. Error I get is circular reference.
So, I'm trying to come up with a dynamic graph title. I highlight the graph title, go to the formula bar, and type:
Code: =CONCATENATE(Sheet1!$C$1," All Servers")
"That function is not valid". Yet, the same function, placed in a cell, works perfectly. I suppose I could use an intermediate cell to hold the concatenated text and point the graph title to that cell, but I dislike having contextless cells like that floating around. Is there a way to apply that formula, or some other with the same effect, directly to the graph title?
I'm trying to create a code that autofills the next empty row in colum A with a number. What I have at the moment is:
'Create Index in Transaction list x = Range("A" & Rows.Count).End(xlUp).Value Sheets("Transaction list").Range("A" & Rows.Count).End(xlUp).Offset(1, 0) = x + 1
If I run this code in isolation from the rest of my code (which is copying and pasting values from one sheet to another) it works, but when I run the full code it only autofills the rows with "1".
I found the thread about how to get the second best result (=LARGE(range,2)) but that can't handle when I have ywo top values and a lower third value. It still return the next value after the top value, which is the same value. I want to get the next lowest vaule.
Example: In this row: 7 7 6 5 5 4
I want to get the value 6 and the formula =LARGE(range,2) gives the second number 7.
How do I get the second value that I want?
Maybe if I use the RANK() formula in some way, but how?
