Vlookup not behaving as expected
excel,excel-formula,worksheet-function
Change to =VLOOKUP(C1,A1:B4,2) In fact, you better use absolute references, otherwise the lookup range will get messed up when you copy the formula down. =VLOOKUP(C1,$A$1:$B$4,2) But really, I would advise dropping VLOOKUP in favour of INDEX and MATCH, which is way more flexible: =INDEX($B$1:$B$4,MATCH(C1,$A$1:$A$4,0)) ...
Writing a MATCH or LOOKUP statement with a precondition
excel,if-statement,excel-formula,match,worksheet-function
I think what you want may be something like: =IF(A1<>"apples","oops!",IF(ISERROR(MATCH(B1,Sheet4!C:C,0)),F2,F1)) assuming only the ColumnC enty is on Sheet4, all others on the same sheet as the formula, and that if A1 does not equal "apples" you want to be warned (in the example with oops!) though you may prefer to...
Adding all the values of the column based on column Header
excel,excel-formula,worksheet-function
You can use something like this: =SUM(INDEX(A4:Z100,0,MATCH("SVYM Total" & "Jun-14",Array,0))) where your data (excluding headers) are in range A4:Z100. I supposed that Array is named range that refers to, in my example, A3:Z3....
Move rows based on criteria within string
excel,vba,excel-vba,excel-formula,worksheet-function
A formula cannot move a row to another sheet. However it can identify which rows to move, perhaps prior to filtering. Please try: =IF(1*MID(SUBSTITUTE(B2," ",""),3,1)>6,"#","") ...
Excel: how to merge duplicate rows of a column , leaving the rest of column data as it is
excel,excel-formula,worksheet-function
Formula to output value from column A only if it is different from previous row is: =IF(A2 = A1, "", A2) but it is not possible to update cells in-place with plain formulas, you can: copy the computed cells and paste special as values back into column A add a...
Runtime error 1004. Need to fetch value from another sheet in same Excel file
excel-vba,excel-formula,excel-2010,vlookup,worksheet-function
I modified the code as below and it worked. I removed WorkSheetFunction and also handled the result value. Sub UpdateFormula() Dim CurrStr As String Dim EndRow As Long On Error GoTo 0 EndRow = Range("A" & Rows.Count).End(xlUp).Row BaseStr = UCase(Range("A2").Value) Application.ScreenUpdating = False For iter = 4332 To EndRow On...
Excel 2013 create column w/specific value from another column?
If B will be new column that the url is in, then use the following formula in A, and drag down. =MID(B2, SEARCH("id",B2)+2, SEARCH("~?",B2)-SEARCH("id",B2)-2) ...
Conditional Formatting of two cells based on a value's presence in a column
excel,excel-formula,worksheet-function,conditional-formatting,countif
The way I understand your requirement, please select N11:O11 and HOME > Styles - Conditional Formatting, New Rule..., Use a formula to determine which cells to format, Format values where this formula is true:, enter: =COUNTIF($B11:$B20,"other")>0 Format..., choose your formatting, OK, OK....
Count frequencies if values of other column are equal to specific text
excel,excel-formula,worksheet-function,countif
Assuming City is in A1 please try: =COUNTIFS(A:A,"London",B:B,"Webdesigner") ...
`10/2` evaluates to `42279.00`?
string,excel,formatting,excel-formula,worksheet-function
Please try applying a formula of the following kind to all your data: =MONTH(B1)/DAY(B1) Having done so you may select the results, Copy and Paste Special over their source (assumed to be ColumnB) and then delete the formulae. Excel has, trying to be helpful, interpreted your entries as dates -...
Match pattern of a list of words and highlight the found results
excel,excel-formula,worksheet-function
Please try selecting ColumnA, HOME > Styles - Conditional Formatting - New Rule..., Use a formula to determine which cells to format, Format values where this formula is true:: =MATCH(LEFT(RIGHT(A1,8),7),B:B,0) Format..., Fill, select your choice of highlighting, OK, OK....
Write several strings in one cell
string,excel,excel-formula,concatenation,worksheet-function
At present the formula might be read as “If this true do that, otherwise if something different is true do the other”, so the result can only be that OR the other. To achieve both that and the other in the same cell only an adjustment to concatenate the two...
Find the nearest set of coordinates in Excel
You could try filling down this formula from G1 as shown below: =LOOKUP(1,1/FREQUENCY(0,MMULT((B$1:C$10-E1:F1)^2,{1;1})),A$1:A$10) For a more accurate formula that takes account of the circular distances try filling down from H1: =LOOKUP(1,1/FREQUENCY(0,SIN((RADIANS(B$1:B$10-E1))/2)^2+SIN((RADIANS(C$1:C$10-F1))/2)^2*COS(RADIANS(B$1:B$10))*COS(RADIANS(E1))),A$1:A$10) ...
Conditional count
excel,excel-formula,worksheet-function,countif
To count any entries use: =COUNTA(F8:F20) To count specific entries use: =COUNTIF(F8:F20,"W") ...
How to export data from a PivotTable to .csv in a specific format?
excel,excel-formula,pivot-table,export-to-csv,worksheet-function
My hunch is PivotTables are irrelevant here (and that this is more "one off" than "routine") so suggest: Work on a copy. Parse data assumed to be in ColumnA with Text to Columns and pipe as the delimiter. Insert a row at the top. In C2: =IF(A1=A2,C1&","&B2,A2&","&B2) in D2: =A2<>A3...
INDEX/MATCH for closest value to a certain date
excel,date,excel-formula,worksheet-function,array-formulas
Following 4 named ranges for simplicity in code: "Dividends": DividendDates (column A); DividendsPaid (column B) "AdjClose": StockDate (column A); StockPrice (column B) try (in column C in "Dividends": {=INDEX(StockPrice;MATCH(MAX(IF((StockDates<=A1);StockDates));StockDates;0))} Assuming that the dividend date for which you want to find the adjusted stock price is in cell A1. And copy...
Combine multiple VLOOKUPs
excel,if-statement,excel-formula,vlookup,worksheet-function
If you wish to work through each of the pairs of columns in order, for an example of three pairs, just: =IFERROR(VLOOKUP(B2:B11,Sheet2!A:B,2,0),IFERROR(VLOOKUP(B2:B11,Sheet2!D:E,2,0),IFERROR(VLOOKUP(B2:B11,Sheet2!G:H,2,0),"No Match"))) assuming that it is B2 that is to be your search term (lookup_value) and that the formula above is in Row2. Where ranges are used as the...
Select adjacent data based on values in cells
excel,if-statement,integer,excel-formula,worksheet-function
Assuming Time is in A1, please try in C2: =INT(A2) In D2: =B2 and in D3: =IF(INT(A3)>INT(A2),B3,"") both first and last above copied down to suit....
SUMPRODUCT formula is only counting some dates within specified ranges
excel,date,integer,excel-formula,worksheet-function
thanks guys, it worked with ron sugestion =SUMPRODUCT((INT('Reg. 2013'!B:B)>=D10)*(INT('Reg. 2013'!B:B)<=E10)) thanks
Conditionally assign unique values
excel,if-statement,excel-formula,max,worksheet-function
If your data is in three columns (starting A2) then maybe: =IF(B2="MD",MAX(C$1:C1)+1,"") ...
Calculate the conditional median value?
excel,excel-formula,worksheet-function,median,array-formulas
Maybe: =MEDIAN(IF(A:A="A",B:B)) entered CSE....
How to remove substring that is in another column in Excel?
excel-formula,worksheet-function
You can use the SUBSTITUTE function like this: =SUBSTITUTE(A1,B1,"") This will take the text that's in A1, and replace the text that's in B1 with nothing. Read more about SUBSTITUTE here....
How to add more criteria to this IF(COUNTIF formula?
excel,if-statement,excel-formula,worksheet-function,countif
With COUNTIF: =IF(COUNTIF(A:A,a2)>15,1,IF(COUNTIF(A:A,a2)>10,2,IF(COUNTIF(A:A,a2)>5,3,IF(COUNTIF(A:A,a2)>=1,4,5)))) ...
RATE() is returning “#NUM!”
excel,excel-formula,excel-2007,worksheet-function
If the successive results of RATE do not converge to within 0.0000001 after 20 iterations, RATE returns the #NUM! error value. Without including a guess for the Guess it is assumed to be 10 percent and that is nowhere near the actual rate that would be required to equate...
Does any date in one range exist in another range of dates?
This formula assumes that the earlier date/time comes first. If that is not the case, use MIN and MAX to ensure the correct comparisons. =AND(D1>=A1,C1<=B1) ...
Return a value from a specific cell address if condition of another cell is met
excel,numbers,excel-formula,worksheet-function
It sounds like you need a vlookup. It's a common spreadsheet problem. Have a look at this answer on stack overflow: Using VLOOKUP() and this http://www.contextures.com/xlFunctions02.html These are for excel but basically all spreadsheets will handle this similarly. Next time, just include the image directly in your question. Makes for...
Set Pass/fail based on 10 answers from each of many individuals
excel,excel-formula,worksheet-function,excel-2011
I'd suggest a separate row for the answers but in matching columns: then something like: =IF(SUMPRODUCT(--(E$1:N$1=E3:N3))>6,"Pass","fail") As @Jeeped points out, the correct answers could be left where they are with only a small adjustment to the formula above but (apart from the lurid colours!) I think the layout shown is...
Detecting what value was in a cell prior to a change
You can undo the change, populate the undone value to a variable then redo the change like so: Private Sub Worksheet_Change(ByVal Target As Range) Dim OldValue As Variant, NewValue As Variant NewValue = Target.Value Application.EnableEvents = False Application.Undo OldValue = Target.Value Target.Value = NewValue Application.EnableEvents = True MsgBox OldValue End...
Top n conditional formatting - with a tie breaker
excel,excel-formula,worksheet-function,conditional-formatting,countif
The ties may be broken with a COUNTIF adjustment. For example a CF rule formula of the kind: =RANK(A1,$A$1:$O$1,0)+COUNTIF($A$1:A$1,A1)-1<7 <7 for the six you specified, though it depends upon when ranking ascending or descending. Ties are broken "in order" - the first found is accepted (count of 1 less 1...
How to create a formula for every row in a column in Google SpreadSheet?
google-spreadsheet,spreadsheet,worksheet-function,formulas
If you want to use OR in Arrayformula you'll need to use +. Can you see if this works ? =ArrayFormula(IF(LEN(H4:H), IF((O4:O = "Yes")+(H4:H = "Yes"),"Yes","No"),)) ...
Return highest number value in column cell plus a related row cell data
excel,excel-formula,max,concatenation,worksheet-function
Please try: =MAX(D:D)&"("&INDEX(A:A,MATCH(MAX(D:D),D:D,0))&")" will only return one year even if the maximum value arises in more than one year....
Identify all instances of a set where any one instance of the set is paired with a specific value
excel,excel-formula,worksheet-function,conditional-formatting,countif
If what you want is all instances of a specific INCnnn to be identified where any one of the specific INCnnn is in the same row as "BREACHED" then Conditional Formatting would do this, say as below assuming your data starts with 1 in A1 and is in three columns....
Combining INDEX/MATCH with COUNTIF
excel,count,indexing,excel-formula,worksheet-function
Though a function not available in versions of Excel before 2007, it seems: =COUNTIFS(A:A,3,B:B,5) met the requirement here....
Separate some values by brackets and decrease a number from a column to another column
excel,excel-vba,excel-formula,worksheet-function,substitute
Assuming First column is in A1, not VBA, in D2: 50 in E2: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2&","&C2,A2,","),",,,",","),",,","") in F2: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE("[50,"&SUBSTITUTE(B2,A2,"")&"]["&SUBSTITUTE(B2,A2,"")&"]["&C2&"]",",,",","),",]","]"),"[,","[") and copy all three down to suit....
Multiple cells testing the same condition in an or statement
Will this do? =IF(MAX(D11:G11)>$I$6,"YES","NO") Alternatively, if you are happy with TRUE or FALSE instead of "YES" or "NO" you can use an array formula: {=OR(D11:G11)>$I$6)} ...
IF statement containing four conditions but single answer (or blank)
excel,if-statement,nested,excel-formula,worksheet-function
Maybe: =IF($L2=1,IF(AND($H2>=50,$O2>=5,$O2<8),"A: Priority 2",""),"") ...
Combined COUNTIF for several columns
excel,excel-formula,worksheet-function,countif
Please try: =COUNTIFS(A:A,"one",B:B,"<>") ...
Excel VBA to generate a list of 100 pseudo-random numbers
excel,excel-vba,random,worksheet-function
You can try the following snippet (adjust it for your particular range and worksheet naming) Sub GenerateRandomList() Dim loop_ctr As Integer For loop_ctr = 1 To 100 Range("A" & loop_ctr) = "=INT(RAND()*3+1)-1" Calculate Next loop_ctr End Sub Open the new Excel Worksheet, put the code in the Sheet1 VBA code...
Replace “Lunch Out, Lunch In” with duration in formula to give Time Out/In difference excluding Lunch
excel,excel-formula,rounding,worksheet-function,time-format
Adding a ColumnE for Lunch, please try: =IFERROR(IF(D2="","",(D2-A2)*24)-E2,"") formatted as number....
Avoid Worksheet_Change event if user changes a certain value
excel,vba,excel-vba,events,worksheet-function
If i understand you correctly... For Row2 = 2 To LastRow2 'value has been already changed, do not recalculate If Cells(Row2, 3) <> Range("I" & Row2) Then GoTo SkipNext Cells(Row2, 3) = Range("I" & Row2) SkipNext: Next Row2 Note: Using below condition, you'll be able to recalculate it again, if...
If the row contains specific text highlight row by row where two columns are the same
excel,excel-formula,worksheet-function,conditional-formatting,countif
Maybe add an AND to your formula: =AND($G1="name",COUNTIF($M1:$N1,M1)>1) ...
COUNTIF with OR Statement
excel,excel-formula,worksheet-function,excel-2013,countif
Ugly, but might suit: =COUNTIFS(E:E;">120";E:E;"<139")+COUNTIFS(F:F;">80";F:F;"<89")-COUNTIFS(E:E;">120";E:E;"<139";F:F;">80";F:F;"<89") Counts all the applicable instances in ColumnE, adds the count of all the applicable instances in ColumnF then subtracts the instances where both ColumnE and ColumnF are within the respective bounds - this last part might not be required. An array formula might be more...
Excel: search for information
This UDF should do the trick - I won't provide a detailed description of what it does beyond the comments you can see in it, as it's not very complex piece of code, but if there is something you don't understand in it, feel free to ask. Option Explicit Function...
Frequency of data occurence in Excel Column based on the certain condition
excel,if-statement,excel-formula,worksheet-function,countif
So after struggling to understand what was going on, here is the simple solution: first let me clarify again: column A: but pasted as text originally and in hhmm format, but you need to reformat them as simple numbers, so you will lose those 0 s at the beginning of...
Insert into a cell the first five characters of the filename
excel,excel-formula,cell,filenames,worksheet-function
Please try: =MID(CELL("filename"),FIND("[",CELL("filename"))+1,5) . @barry houdini has kindly pointed out that the above is flawed (in a way that may not often be an issue but could at times be very confusing): It's better to use a cell reference in CELL function with this formula, e.g. =MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,5) - that ensures...
Excel formula for working with multiples
excel-formula,rounding,worksheet-function
It's worth noting that Excel has built-in functions for working with multiples: CEILING and FLOOR (in newer versions you have CEILING.MATH and FLOOR.MATH). In your case, this should work: =CEILING(A1,20)-A1 ...
Compare 2 set of cells and count
excel-formula,worksheet-function
From my understanding you have seven columns, like this: You would like to make a ratio between a combination that includes all seven columns (from A to G in the picture), and the combination of the first six columns (from A to F in the picture). In this case you...
Combine two cells in sumproduct
excel,excel-formula,worksheet-function,countif,array-formulas
Just wanted to check if @Byron answer in the comment works. It works, the result is 7. {=SUMPRODUCT(--(A2:A17>128),--(A2:A17<136), --(NETWORKDAYS(IF(ISBLANK(C2:C17),B2:B17,C2:C17)+0,TODAY()+0,0)<=30))} ...
Why is the syntax for a worksheet IF() function with optional parameters different from UDF?
excel,vba,excel-vba,if-statement,worksheet-function
"Optional argument" can apparently have two distinct meanings for Excel functions: the optional argument may be omitted; the value of the optional argument may be omitted. Sometimes both are allowed, sometimes only #2 is allowed. Look at the documentation for IF: IF(logical_test, [value_if_true], [value_if_false]) [...] value_if_false Optional. [...] If logical_test...
Modifying an existing time formula
excel,if-statement,time,excel-formula,worksheet-function
Maybe: =IF(AND(A1>=7.5/24,A1<=8.5/24),8/24,A1) ...
Count rows that have cell don't existing in a list in Excel
Way №1 In cell H2 use: =IF(ISNUMBER(MATCH(F2,$J$3:$J$15,0));"exist in list","not exist in list") and drag it down. Then in C12 you can use: =COUNTIF(H2:H8;"not exist in list") Way №2 In C12 use: =SUMPRODUCT(1*NOT(ISNUMBER(MATCH($F$2:$F$8,$J$3:$J$15,0)))) This is an array formula, so type the formula then press CTRL+SHIFT+ENTER. Curly brackets will automatically appear at...
The last & the first day of a month in Google Spreadsheets
google-spreadsheet,spreadsheet,worksheet-function,formulas
If you had your base date in A1, the following formula would be good for the first day of the previous month: =date(year(A1),month(A1)-1,1) It's even simpler to find the last day of the previous month: =eomonth(A1,-1) ...
Why 'Application.Calculation=[value]' statement in Excel VBA erases Clipboard Memory? [duplicate]
excel-vba,clipboard,copy-paste,worksheet-function
Is it true? Apparently so. I had never noticed; but I just tested it and that's what it does. So yes, it's true. Why? Well, who knows. We'd have to ask the Microsoft developers what was going through their heads, or whether this was at all intended. But I don't...
Fixed formula when inserting rows
excel,excel-formula,worksheet-function,sumifs,excel-indirect
Please try: =SUMIF(INDIRECT("'ITEMS CONTROL'!$B$2:$B$20000"),B$1,INDIRECT("'ITEMS CONTROL'!$C$2:$C$20000")) ...
Count how often a value in a cell in one specific column is not the same as the value in the same row of a specific other column
excel,sum,excel-formula,worksheet-function,countif
Hopefully: =COUNTIFS(A:A,"O",B:B,"<>"&"O")+COUNTIFS(A:A,"R",B:B,"<>"&"R") For equal: =COUNTIFS(A:A,"O",B:B,"O")+COUNTIFS(A:A,"R",B:B,"R") ...
Using Wildcards to Extract Text from Cell String Containing a Specific Value/Character
string,excel,extract,wildcard,worksheet-function
Please try, in C2: =SUBSTITUTE(LEFT(SUBSTITUTE(B2," ","|",(LEN(B2)-LEN(SUBSTITUTE(B2," ","")))),FIND("|",SUBSTITUTE(B2," ","|",(LEN(B2)-LEN(SUBSTITUTE(B2," ","")))))-1),",","") in D2: =MID(SUBSTITUTE(B2," ","|",(LEN(B2)-LEN(SUBSTITUTE(B2," ","")))),FIND("|",SUBSTITUTE(B2," ","|",(LEN(B2)-LEN(SUBSTITUTE(B2," ","")))))+1,LEN(B2)) ...
Insert characters into string in different locations
string,excel,excel-vba,text,worksheet-function
Please try, Record Macro and: =LEFT(A1,4)&"-"&MID(A1,5,2)&"-"&MID(A1,7,2)&"T"&MID(A1,9,2)&":"&MID(A1,11,2)&":"&MID(A1,13,2)&"Z" ...
IF statement to show if ALL cells in a row have the same value
excel,excel-formula,worksheet-function,countif
Please try: =COUNTIF(C2:G2,C2)=5 ...
Differences between two columns [closed]
excel,if-statement,excel-formula,worksheet-function,countif
Assuming your data starts in Row1 please try: =IFERROR(MATCH(A1,B:B,0),"#") in Row1 and copy down to suit. # indicates the corresponding row's ColumnA value is not found in ColumnB. A number indicates the row in ColumnB that matches the ColumnA value for the row the value appears in. ...
How to find a difference between previous and current values in Excel Cell?
excel-vba,range,worksheet-function
In order to find a difference between new and old values entered, for example, in "D1" (on cell change event) and display it cell "E1", the general solution is shown in following code snippet (see Listing 1): Listing 1 Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Target.Column = 4 And...
Matching columns between sheets and transfering names
excel,vba,excel-formula,worksheet-function
I think what you want, is: Turn on Record Macro: In H1 enter: =IFERROR(INDEX('Sheet #1'!A:A,MATCH(I1,'Sheet #1'!B:B,0)),"") and copy down to suit....
Store an integer in a cell and use that to set the number of column gaps
excel,if-statement,excel-formula,offset,worksheet-function
May not be quite right but hopefully 'configurable' to suit. Assumes $500 is entered in C10 and (at least for the time being) 1 will not be entered in C5. Please try in D10 and copied across to suit: =IF(MOD(COLUMN()-3,$C5)<MOD(COLUMN()-4,$C5),$C10,"") A quick fix for the above failing to populate all...
If there's a value in the specified range, highlight row
excel,count,excel-formula,worksheet-function,conditional-formatting
To highlight every row in the range 2:1986 where any cell in columns AQ to JK inclusive in that row is populated, use a Conditional Formatting formula rule of: =COUNTA($AQ2:$JK2)>0 with highlighting of your choice and the Applies to range set to: =$2:$1986 ...
Match Function in Specific Column Excel VBA
excel,vba,excel-vba,match,worksheet-function
What you mean to do is better served with Range.Find. Dim rngtrg As Range, rngsrc As Range Dim ws As Worksheet Set ws = ActiveSheet Set rngsrc = ws.Range(ws.Cells(1,colnumvar),ws.Cells(1000,colnumvar)) Set rngtrg = rngsrc.Find(1,...) rowvar = rngtrg.Row ...
How to filter rows that have specific character at the end in Excel?
excel-formula,worksheet-function
Check whether these helps you: http://www.excelforum.com/excel-general/750201-excel-filtering-by-last-2-characters-help-needed.html http://www.extendoffice.com/documents/excel/925-excel-sort-last-character.html ...
Formula that will search cells in table to find a word
excel,search,excel-formula,cell,worksheet-function
MATCH allows wildcards, so: =MATCH("*Apple*",A:A,0) should return the row number in which Apple is in a cell in ColumnA. If more than one instance will return only the first....
Select value from cell having “today's” date in the same row
excel,date,excel-formula,vlookup,worksheet-function
If your two columns are A an B please try: =VLOOKUP(TODAY(),A:B,2,0) ...
Conditional Formatting for many different values
excel,excel-formula,match,worksheet-function,conditional-formatting
If you have two lists of names, say in ColumnA and ColumnI, and wish to highlight those in A that feature in I please select columnA and try applying a Conditional Formatting formula rule of: =MATCH(A1,I:I,0)>0 ...
Sum Formula acts weird when added text to it
It's most likely because of gradual errors introduced during the accumulation of all those value. I suspect that, if you instead used: =SUM(I7:I128) - 36286,54 you'd get a very small, but non-zero, number. And indeed you do, if you examine the graphic below. To solve it, I'd suggest rounding the...
HTML code as text in Excel
excel,if-statement,escaping,excel-formula,worksheet-function
Please try escaping the double quotes: =IF(A2<0,"<span style=""font-size:16px"">& #10003;</span>",A2) ...
Excel formula to get cell color
No, you can only get to the interior color of a cell by using a Macro. I am afraid. It's really easy to do (cell.interior.color) so unless you have a requirement that restricts you from using VBA, I say go for it.
Get number of bills from range of dollar amounts - cascading larger to smaller bills
excel,integer,excel-formula,rounding,worksheet-function
UNTESTED. With Kids in A2 and 50,20,10,5,1 in C1:G1 then perhaps in C2 and copied across and down: =INT(MIN($B2,$B2-SUMPRODUCT($B$1:B$1*$B2:B2))/C$1) The results might then be summed by column (excluding the labels) to get the count by denomination and each sum multiplied by the column labels (eg =SUM(C2:C10)*C$1) then the sums summed...
Sorting Worksheet data by column values using Excel VBA
excel,vba,sorting,excel-vba,worksheet-function
You can sort your Excel Worksheet in ascending order using VBA statement like the following: Columns("A:XFD").Sort key1:=Range("AD:AD"), order1:=xlAscending, Header:=xlYes Note: in the column range Columns("A:XFD") instead of XFD enter the last used column pertinent to your case, e.g. Columns("A:DD"). Hope this will help....
COUNTIF combined with OR
excel,excel-formula,worksheet-function,countif
You could add multiple countif's - =if(A1<>"",countif(a2:a5,"Yes")+countif(a2:a5,"Si")+countif(a2:a5,"True"),"")
Conditional Excel Sum based on dates range
excel,worksheet-function,conditional-operator,sumifs
In order to get the conditional Sum for specified date range (Nov 2014), correct your syntax as shown in the sample below: =SUMIFS(E9:E1000,B9:B1000,">="&DATE(2014,11,1), B9:B1000,"<"&DATE(2014,12,1)) Note: this solution is pertinent to the US date format: mm/dd/yyyy, so you may need to correct it per your case depends on how DATE() function...
Change a full column of such as F01U123456 to F.01U.123.456 [closed]
string,excel,excel-vba,excel-formula,worksheet-function
A formula is sufficient: =LEFT(A1)&"."&MID(A1,2,3)&"."&MID(A1,5,3)&"."&MID(A1,8,3) ...
Formula to increment by volume and issue number, each in sets of three
excel,integer,excel-formula,worksheet-function,modulus
In Row3 please try: =INT(ROW()/3)&"."&MOD(ROW(),3)+1 and copy down to suit. For other rows you would need to add an offset (+n)....
INDEX/MATCH, or another function?
excel,excel-formula,match,vlookup,worksheet-function
The "near miss" looks as though it used a helper column. Insert a ColumnC into Sheet3 and in C1 there enter: =A1&"|"&B1 Copy that down to suit. In your other sheet in ColumnP (Row1 ? - or adjust the N and O row references to suit) enter: =VLOOKUP($N1&"|"&$O1,Sheet3!$C:$F,2,0) and copy...
Find the maximum value associated with a specific value in a different column
excel,if-statement,excel-formula,worksheet-function,array-formulas
Please try: =MAX(IF(A:A=2,B:B)) entered with Ctrl+Shift+Enter....
Find first 30 string matches & paste at location A, then paste remaining string matches at location B
excel,vba,copy-paste,string-matching,worksheet-function
Sub As_Of_Analysis_Sorting() Dim lr As Long, lr2 As Long, r As Long, x As Long Dim i as Long Set Sh1 = ThisWorkbook.Worksheets("All Trades") Set Sh2 = ThisWorkbook.Worksheets("As-Of Trades") Sh2.Cells(1, 1).Value = "Account" Sh2.Cells(1, 2).Value = "Amount" lr = Sh1.Cells(Rows.Count, "A").End(xlUp).Row x = 2 i = 0 For r =...
Finding Max Values in multiple Ranges specified by additional condition
excel,excel-vba,max,worksheet-function,cells
If Class is in B1, maybe: =MAX(IF(B:B=B2,A:A)) in C2 entered with Ctrl+Shift+Enter and copied down to suit. oOPs! OP did ask "with a pivot table": ...
Filter information in two columns
excel,excel-formula,match,worksheet-function,input-filtering
With data like: In D1 enter: =IF(ISERROR(MATCH(A1,B:B,0)),1,"") and copy down and then in C1 enter: =IFERROR(INDEX(A:A,MATCH(ROW(),D:D,0)),"") and copy down. This results in: ...
Percentage of non-numeric answers in PivotTable?
excel,pivot-table,percentage,worksheet-function,countif
If your results are in Column A for example, then something like: ="Less than $1 million: "&(COUNTIF(A:A,"Less than $1 million")/COUNTA(A:A))*100&"%" should do you. If you have a header in Row 1 then change COUNTA(A:A) to COUNTA(A:A)-1 based on your example above, this formula would return "Less than $1 million: 62.5%"...
Show number sign at the right side of number using cell formatting
excel,formatting,excel-formula,worksheet-function,conditional-formatting
Assuming integers only, please try a Custom Format of: 0"+";0"-";"-"; Re Edit: Select the range to be formatted (I chose N for illustration), HOME > Styles – Conditional Formatting, New Rule…, Use a formula to determine which cells to format, Format values where this formula is true: =N1=1 Format…, Number,...
VLOOKUP query help required - count from a range
excel,excel-formula,vlookup,worksheet-function,countif
You need to be consistent with spellings or this won't work, but for standings B2 you might try: =IF(COUNTIFS(race1!C:C,A2,race1!A:A,"<>D",race1!A:A,"<>R")=2,20,IF(COUNTIFS(race1!C:C,A2,race1!A:A,"<>D",race1!A:A,"<>R")=1,7,-15)) and in C2: =COUNTIF(race1!$C$2:$C$11,A2)*10 both copied down to suit. Edit for number of cars in column B change first formula to: =COUNTIFS(race1!C:C,A2,race1!A:A,"<>D",race1!A:A,"<>R") and for points put first formula in C2 (copy...
Discrepancy between results of EXACT function and Conditional Formatting
excel,excel-formula,worksheet-function,conditional-formatting,exact-match
It seems there is a 255 character limit to the Conditional Formatting, Highlight Cells Rules..., Duplicate Values... rule. To me plausible since consistent with the limit for characters in a formula in VBA. A simple way to check is to apply such a rule to both the column with your...
Conditionally format one cell based on values in a column
excel,datetime,excel-formula,worksheet-function,conditional-formatting
Please format B2 green with standard fill, then apply the following Use a formula to determine which cells to format, Format values where this formula is true: =match(B2,L:L,0)>0 with formatting (red) to suit and Applies to B2....
Multiplying column values in Google Spreadsheet with ability to add/delete rows
google-spreadsheet,spreadsheet,worksheet-function
The following ArrayFormula() spans from row 5 ("Books Name") to row 14 ("Total Price"), so adding rows or deleting them should not be a problem: =ArrayFormula(if((C5:C14="Books Name")+(C5:C14="Total Price")=1,"",H5:H14*I5:I14)) However, since the formula will have to reside in the column J, the following formula is even more useful: =ArrayFormula(if(C5:C14="Books Name","Amount",if(C5:C14="Total Price","",H5:H14*I5:I14)))...
VBA remove matching first & last names across 2 worksheets
string,excel,vba,match,worksheet-function
Loops through all values in Sheet1 Column B. If that value is found in Sheet2 Column E, the entire row in Sheet1 is deleted. Then it loops through all values in Sheet1 Column C. If that value is found in Sheet2 Column F, the entire row in Sheet1 is deleted....
Lookup a Value and Display Column Header
excel,if-statement,excel-formula,lookup,worksheet-function
I am assuming the numbers run 1>12 in your example merely for illustration and 1 is in A2, hence suggest: =IF(ISERROR(MATCH(D1,A:A,0)),IF(ISERROR(MATCH(D1,B:B,0)),C1,B1),A1) ...