I can't get this to work. What am I doing wrong? I keep getting a #VALUE! error.
=SUMIFS($G$2:$G$31,$L$2:$L$30,F37,$B$2:$B$30,G$36)
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.
=IF(A2=A2,"CN","US") doesn't work
ie
**
US
US
**
with give me this
** CN
US US
US US
** CN
Ok here is my dilemma, I am trying to calculate gains and losses by organization. I created a sumifs formula that works on a single page, but it doesn't work on multiple tabs on my summary page.
Here is the data:
HRS DEVIATEDTAILORG0.2OPS-0.3OPS0.8MX0.5OPS-2.5MX-1.5WX-1.5WX
Here is the formula on the same page as data:
=SUMIFS(A26:A40,C26:C40,"wx",A26:A40,"
i am trying to us the SUMIFS formula to work out if a rep is below target on selling brands.
I have two worksheets summary and data, Summary worksheet is where i am trying to do the sumif formula against a sales average taking the information from the data worksheet
I have account manager name, brand, sales, DSI
I tryed the below sumifs formula but its coming back as 0
Please could someone have a look and point out the error in my ways
=COUNTIFS(data!$A$2:$A$11,B17,data!F2:F11,"<B2)")
The first count if should count how many times the account manager appears in the brand and the second count is if his DSI on the data worksheet is higher that the target dsi on the summary sheet
i checked and checked and my IF function just gives me the wrong answer... attached is just a sample data..i have over 230 lines to check actually..
View 4 Replies View RelatedI 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.
View 11 Replies View RelatedAs 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.
View 9 Replies View RelatedI need to do a SUMIFS with 2 sets of Criteria, but I need the second Criteria to be an OR Statement. The numbers I am adding up is in Column E, with any where from about 20,000 to about 60,000 records (will fluctuate all the time). The first Criteria is easy - match up the name in AG12 with any of the names in Column C. The second criteria is to match up the numbers in Column A with any of the numbers in AD1 through AD40.
I tried this but it didn't work: =SUMIFS(E:E,C:C,AG12,A:A,AD1:AD40). I've alos tried a million other variations of that, imbedded SUM(IF( statements, SUMPRODUCT statements, and DSUM statements and no luck. I can't find anything that allows me to make that second second criteria look at the numbers in Column AD as being AD1 or AD2 or AD3 so on and so forth.
I'm trying to put together a SumIfs formula to find the total count of procedure codes during a certain time period, for a given market.
I have the Market column, Procedure Code column, Date column, and count of Procedure Codes column.
The range of Procedure Codes I want to total on are in cells C4:F4 and they are:
99307993089930999310
It's fairly straightforward with the dates in the SumIfs as well as markets, but I'm not able to get the range of Procedure codes to work like I want. These 4 codes are just the criteria that will use to pull the totals from, which comes from a much larger dataset.
See the excel sheet [URL]
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
[Code]....
im trying to use below formula using SUM and two IF conditions and 1 left formula
Cond 1
Cond 2
Value
22
m9
10
23
m9
10
[code]....
the following formula works to capture the sum of combination 22 and m9:
{=SUM(IF((A2:A6)=A8,IF(B2:B6=B8,C2:C6,)))}
22
m9
20
24
m8
10
however, in my original table, the 22, 23 etc are values with 6 numbers as shown below
Cond 1
Cond 2
Value
2201
m9
10
2301
m9
10
[code]....
following formula didnt work;
{=SUM(IF(LEFT(E2:E6,2)=E8,IF(F2:F6=F8,G2:G6,)))}
22
m9
0
23
m9
0
why the secund formula didnt capture anything when i added left to the first one.
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
[code]...
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
it works fine.
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.
View 9 Replies View RelatedI 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:
Range("SsNo1").Resize(d, f).Offset(a, 0).Select
Selection.Sort Key1:=Range("FRSet1"), Order1:=xlDescending, Header:=xlGuess _
, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Is this is just an example, but the logic should hold true. Data for example is in A1:C5. I want to use a SUMIFS statement that will look at type of Vehicle and Dates. So for the example in cell D1 (I wanted to add only Cars sold between May 1-2), but my current equation = 0, where I am expecting 20
=SUMIFS(C1:C5,B1:B5,B3:B4,A1:A5,A1)
Why doesn't Cancel (Escape key) work while Sleep API (kernel32.dll) is running?
I was looking at making a minor change to a solved thread (see [URL] ....)
I would like the user to be able to exit the form by pressing Escape. I altered the start and end of the sub to below but it didn't work:
START:
[Code]....
END:
[Code] .....
I always used Office 2003 .. I just installed Office 2007 to try it out. But everything doesn't work in Excel 2007, which does work in Excel 2003.
I added an example of the list, but here are the codes:
Sheet1
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")
[Code] ........
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
Why doesn't a three column formula work?
Column C is Credit, D is Debit, E is Balance
I put the following formula in the Balance column
=E21+C22-D22
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?
I'm trying to simplify a formula that I'm using in a spreadsheet that's being used mostly in Excel 2000.
2 reasons I'm doing this:
1) It doesn't work if the worksheet hasn't been saved yet.
2) It's ugly
=DATE(YEAR(DATEVALUE(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,3) & " 20" & MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+5,2))),MONTH(DATEVALUE(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,3) & " 20" & MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+5,2)))+1,0)
The formula returns the last day of the month of the name of the sheet. For example, if the sheet is names "Mar 07", it returns 3/31/07. (yeah, when the 2100 issue becomes a big deal, I'm not going to be ready but I have 92 years to think about it)
Even if I can't make it prettier, does anyone have a way to get the sheet name into a formula without VBA Code in a file that hasn't been saved.
I am trying to run a macro in the background. I have added the Application.ScreenUpdating = False (and True at the end) to my code but no luck.
View 9 Replies View RelatedI have a sumif formula in one workbook that relates to information off of another workbook...when the other workbook is open the information populates no problem when you close the other workbook I get the #VALUE error. Is this typical of SUMIF when referring to another workbook? I have a vlookup function that refers to the same workbook and it works fine when the other workbook is closed?