Sum Cells Which Meet Certain Values In Noncontiguous Columns
I'm trying to sum cells which meet certain values in noncontiguous columns. It's difficult to explain so I've attached a sample wookbook.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
SumIf Two Columns Meet Criteria
On the attached example i would like a formula in F2 which sums all the values where column equals "Smith" and column B equals "Golf European". I'm assuming it would involve the SumIf formula but i'm not sure if this is the best solution when the criteria is in two columns?
View Replies!
View Related
Macro: If Two Columns Meet Condtion Then Highlight Row
If two parrallel cells in column D & G equal "0" then highlight that row with ======== (Thin Horizontal stripe) Example: D2 and G2 both = "0" ( Macro Applies. Row 2 is highlighted with Thin Horizontal Stripe). D2 and G3 both = "0" (Macro does not apply since they are in different rows) D2= 1 G2=0 (Macro does not apply since they both dont meet condition.)
View Replies!
View Related
Sum Values That Meet Certain Criteria
I have a spreadsheet that is set out to look similar to a MS Project plan, with dates across the top, users in column A and Assignments in column B. In the corresponding cell that matches the user, date and assignment they will enter the number of hours they anticipate working on the assignment. I then have a summary sheet that covers all assignments, week ending dates and users. I want to be able to calculate the hours and copy it into the cell in the summary sheet that matches the Day, User and Assignment. I have tried using SUMIF, but this requires the criteria cells to all be in columns, but with this sheet the dates are in a row.
View Replies!
View Related
Delete Rows Where Multiple Columns Meet Criteria
I am looking for an Excel VBA code that can delete pair of rows that has certain criteria. I have included an example in the attachment and highlighted the rows that should be deleted. Since the values in each coloumn is going to change every day , I am looking for a dynamic code. The objective is to: To delete rows that has same code (columnd D), same basis (coloumn E), same Effective (coloumn H), value of TT (Column J) is either TI or TO and has offsetting Amounts (L) ie the sume becomes zero. Example pair of row 7 and row 8 as well as pair of row 12 and row 13 should be deleted. TO and TI are actually transfer in and transfer out. Since the TI and TO for these rows make the amount zero for same code and same basis on the same effective date, I do not want to include this in the spreadsheet.
View Replies!
View Related
Delete Rows Where 2 Columns Meet Multiple Criteria
I have a file with many intercompany transactions. There is a range of business unit numbers that if anyone one number is in both Column B "Bus Unit" and Column E "Affiliate" I want the row deleted. Please see the attached, it is more clear I hope. The two yellow rows would be deleted. The list of business units that I would want to never be on the same row is "4, 7, 41, 44, 46, 51"
View Replies!
View Related
Calculating On Values On Calender That Meet A Given Criteria
I find this difficult to explain, so have attached an example. I need to total all entries on a calender that are entered after a specific date. EG: If the given date is 15 Sept 08, I need all values in Sept  Mar to be totalled. I am measuring data on numerous spreadsheets, all others have specific start dates and finish dates, so its a simple a1b1 to get the number of days, multiplied by costs. This particular spreadsheet is laid out in calender format, hence the problem)
View Replies!
View Related
Copy NonContiguous Columns
how to reference an entire column only knowing the column numbers, not letters. The response below was given. Range(Cells(1, 40), Cells(1, 44)).EntireColumn.Select I have a similar situation, except that I use the FIND command to return a column number based on a string match. X_Column = Sheets ("Sheet1").Range("A1:IV1").Find("X").Column Y_Column = Sheets ("Sheet1").Range("A1:IV1").Find("Y").Column Z_Column = Sheets ("Sheet1").Range("A1:IV1").Find("Z").Column Because of this, I can recieve nonsequential column numbers, simply based on where "X", "Y" or "Z" are in the sheet, which can be anywhere. I would like to select X_Column, Y_Column, and Z_Column and copy/paste to another sheet, hopefully without having to loop. I have a feeling that an answer refering to Named Ranges is coming, but I'm very green on the subject.
View Replies!
View Related
Number Of Columns In NonContiguous Range
Consider the following range (in my case a PivotTable):A1:E7 I create a Named Ranges that corresponds to column 1:3 and 6 from the range above. i.e : A1:C7,E1:E7 I need to find out the number of columns of my range, this is because it can change in the future, and I have an offset formula needing this information. When i type =columns(MyNameRange) it returns #REF! as there are 2 areas in my Named Ranges. I am expecting 4. I have written the a VBA User Defined Function that does the job, but I would like to know if there is no native solution to get this information. Function TotColumns(rng As Range) Dim Cols As Integer For Each Ar In rng.Areas Cols = Cols + Ar.Columns.Count Next TotColumns = Cols End Function
View Replies!
View Related
Look For Cells That Meet 2 Criteria
I have built the following code which should look through a data sheet and then work out the instances where it meets the following criterias: = "Client A" = "First letter of surname is "a" The code is as follows: Public rowcn Sub compare() Dim rngTemp As Range Dim intCounter As Integer loop_col = "Client A" data_sheet = "Data" target_sheet = "Summary" rowcn = 2 Do.............................
View Replies!
View Related
Match Value To A NonContiguous List Of Values
I have a list of values and value ranges (11 total) that I'm trying to match to values in Column A, but my formula has too many nested "IF" functions to work. My formula is fairly simple, so I'm hoping it will do a better job of describing my problem than I. =IF(4>A2>0,"1",if(37>A2>24,"2",if(A2=39,"3",if(45>A2>41,"4",if(A2=48,"5",if(A2=62,"6",if(73>A2>64,"7 ",if(A2=75,"8",if(A2=80,"9",if(107>A2>102,"10",if(A2=141,"11","")))))))))))) There should be a positive result if the cell value matches any one of the following: 13 2536 39 4244 48 62 6572 75 80 103106 141 In the formula I have numbered the results from 1 to 11, strictly for illustrative purposes. A return of X, 1, YES, etc.
View Replies!
View Related
Replace Same Value Within NonContiguous Ranges With Different Values
This time I want to replace: number "99" in range("B:C") to number "1"; number "99" in range("E:F") to number "2"; number "99" in range("H:I") to number "3"; Sub Replacing() Dim rRange As Range Dim lArea As Long Dim Co As Byte Dim NaCo As Byte NaCo = 99 Set rRange = Range("B:C,E:F,H:I") With rRange For lArea = 1 To .Areas.Count With .Areas(lArea) Co = Choose(lArea, 1, 2, 3) .Replace What:=Co, Replacement:=NaCo, LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False End With Next lArea End With End Sub
View Replies!
View Related
Pass Values Between Two Non Contiguous Ranges
I have Workbooks("A") and Workbooks("B") open. Workbooks("A") contains Sheets("Sheet1"). Range("mySource"). Workbooks("B") contains Sheets("Sheet1").Range("myTarget"). Both ranges have been created by joining multiple ranges, in this way: Union(Range("C1:C13"), Range("K1:K2"), Range("K5:K9"), Range("K14"), Range("Q6"), _ Range("I18"), Range("B20:P20"), Range("B24"), Range("C26:E26"), Range("C29"), _ Range("B34:B40"), Range("B44:Q50")).Name = "mySource" 'or "myTarget" So: both ranges contain the same number of cells with the same addresses, and they have been added in the same order. However, if now I try to pass all values from mySource to myTarget, in this way: Workbooks("B").Sheets("Sheet1").Range("myTarget").Value = _ Workbooks("A").Sheets("Sheet1").Range("mySource").Value the result is a complete mess. Only the first "subrange" of mySource ("C1:C13") is passed to myTarget, and pasted in each of its "subranges", sometimes by rows and sometimes by columns...
View Replies!
View Related
Replace Different Values Within NonContiguous Ranges
I had some code working fine in office 2003: Sub Replacing() Dim rRange As Range Dim lArea As Long Dim Co As Byte Dim NaCo As Byte NaCo = 99 Set rRange = Range("B:C,E:F,H:I") With rRange For lArea = 1 To .Areas.Count With .Areas(lArea) Co = Choose(lArea, 1, 2, 3) .Replace What:=Co, Replacement:=NaCo, LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False End With Next lArea End With End Sub the problem is that it's not working for Office 2000
View Replies!
View Related
Colour Any Cells That Meet The Criteria
I want this macro todo is to start at the top of columns A to J and work down the column and colour any cells that meet the criteria. Range(“A1”).Select Do Until ActiveCell = "" If ActiveCell > 0 Then Selection.ActiveCell.Interior.ColorIndex = 5 Else ActiveCell.Offset(1, 0).Select End If Loop End Sub
View Replies!
View Related
Highlighting If A Range Of Cells Meet A Value
I have a worksheet 200+ rows with 7 columns (euro lottery numbers) I had great help last night to highlight any matching numbers. I'm getting lazy now, can anyone help with a formula to highlight a cell in an adjacent column when 2 or more numbers in the corresponding cells match the winning numbers, I know all I have to do is look down the columns and check but if a simple formula could be inserted it would save me perhaps missing something.
View Replies!
View Related
Listing Cells That Meet A Criteria
I have a list of items (TR Sets) from 1 to 96 in the range B4:B99, with values corresponding to each one (Acid Number) in the range C4:C99. I would like to make a separate list that names each TR Set (using numbers 196) that has an Acid Number > 0.1 . Is there a formula to do this?
View Replies!
View Related
Sum Range Where Corresponding Cells Meet Criteria
I want to sum all cells in column A that have one of several tags in an adjacent cell in column B.  Some of the rows in column A are not tagged  There are currently 3 tags that are valid in column B (I, S, R), but this could increase (or reduce) in the future, say to I, S, R, E  The possible tags are contained in a named range, but don't have to be  It's not possible to restructure the data because the columns are in a scratch sheet, where a variety of calculations are created on the fly in the column. I've attached an example, which might make it clear  There are several (up to 10) sheets like this in the workbook with lots of different calculations that are then summarised in to some analysis.  The overall objective of the workbook is to be able to; a) include/ exclude individual calcs for any one column b) include/exclude a country  solved
View Replies!
View Related
Counting Cells If They Meet Multiple Criterias
I have a list of brands in one worksheet that are abbreviated (Brand 1 = "AB"). In another Worksheet I have a list of products that start with various brand abbreviations (ex: AB12345, BP12345), and in another column on the worksheet I have codes that represent certain characteristics of that style ("1"=flat shot), "2"=shot on model, etc). Now what I want to do is count how many instances I have of products that begin with "AB" and have a "1" in the other column so I can get a count of how many flat shots i have to do for that brand. So far I am using the below code to get a TOTAL count of products that start with "AB", but i cant figure out how to write it so that it checks additional criteria in another column. =SUMPRODUCT((ISNUMBER(SEARCH(A3,(OFFSET('photo list'!G2:G5001,,,,)))))) A3 contains the text "AB" and 'photo list'!G2:G5001 contains the list of products that may or may not contain the text string "AB". Column S (not shown in this code) contains the codes for how to shoot. Hope someone can make sense of this and give me a hand.
View Replies!
View Related
Highlight Row If Cells In Row Meet Multiple Conditions
I have a spreadsheet of actions/tasks. I have a column with the expected completion dates of each action and another column stating whether the action/task is "open" or "closed". I would like to highlight any actions that have gone past their expected completion date and are marked as still being "open". Obviously i dont want any row that are closed to be highlighted. I know the =TODAY() B1>0 formula will highlight the dates cell that have expired but how do i extend this formula to what i require.
View Replies!
View Related
Formula To Return True/False If A Row Has 6 Contiguous 0 Values
I have number data in columns F through AK. In column AL I want a formula that will look in columns F through AK from the left to right. Once it identifies a positive value I want the formula to identify if after that positive number there are any occurances where there are 6 0's in a row (anywhere up until column AK). The formula can return a True or False. I want to drag the formula down across many rows of data. If the entire row contains 0's, I do not want it identified as a TRUE
View Replies!
View Related
How Do I Count Nonblank Cells That Meet Criteria In Another Cell?
I need to add nonblank cells (cells have text) in a column that equal the criteria of another cell. So I want to add the cells in Column C that have text but also equal the date in Column A (which is equal to the date in Cell A1). I have tried the follwoing: =SUM(IF(A2:A19=A1,IF(C3:C19="x",1,0))) =COUNT(IF((A2:A19=A1),C2:C19)) =IF((A2:A19)=A1,COUNTIF(C2:C19,"x"))
View Replies!
View Related
Grouping Contiguous List Into NonContiguous Groups
I would like to be able to count the amount of entries in column C and depending on the amount group them in either groups of 3 or 4, all names would be unique...so if there are 14 names in the list they would need to be grouped in to two groups of 4 and two groups of 3, if there were 19 then 4 groups of 4 and 1 group of 3 etc to a maximum 50 people, the results could appearon a seperate worksheet say pasted on to the worksheet starting with the groups of 3 (so paste a group of 3 then skip 3 rows then paste groups of 4 skip 2 rows, the row skipping is to allow seperation and manual entry of extra data). There will never be groups of 5 or more and never less than 3
View Replies!
View Related
Count Number Of Cells That Meet Specific Conditions  Error Messages
I have a spreadsheet which is linked to several other worksheets. I have managed to include formulas to count how many cells have numbers between 101 and 5000 by using this formula  =sum((h2:h500>=101)*(h2:h500<=5000)) but now I want to count the number of cells in another worksheet that are equal to or less than zero. When I use the same formula as above it counts all the blank cells. I have tried using a countblank formula and then deducting this from the result, but unless the other worksheet is open the countblank formula does not work.
View Replies!
View Related
Sort Columns With Multiple Values In Cells
My columns are labeled A B C D etc... If the product I am creating the table for falls into one of those categories I place a 1 in the cell. For instance, ProductA falls into the A and B categories, so column A will get a 1 and column B will get a 1. This is so I can sort all of category A's Products etc... I am looking for a formula to put in a column that would Say what categories ProductA falls into. ie Since ProductA falls into the A and B catergories but not the C and D catergories, This final column would say something like AB, or A,B or something. Alternatively, I could make a single column entiltled 'categories' and put something like 'A B' in ProductA's column. In this case I would need a way to sort all A's or sort all B's and not just sort all 'A B'
View Replies!
View Related
Clear Noncontiguous Cells
Am looking for routine that can be used to delete the contents of cells from multiple, noncontiguous cells throughout a single worksheet. I would like to place a "button" or some form of toggleswitch on the worksheet that will clear all entries at once. I see a thread "Macro Deleting Wrong Cells" that references a VBA subroutine called "DelCells". I happen to be running Excel 2007 and am still adjusting to the new menu "structure" :)
View Replies!
View Related
Export NonContiguous Column Cells
I have a spreadsheet with multiple columns. I need to save values from each specific column, but they are not noncontiguous, to a .txt or .csv file. In other words, I may need B:10, B15, B12, B25, etc. saved as one string in a text file. I have been working on this for about 15 hours and can't seem to get it to work.
View Replies!
View Related
Conditionally Summing Noncontiguous Cells
Lets say I have values in A1, A3 and A5 and I want to to build either a sumif or an sum if array formula to conditionally sum them. How do I do this, assuming I don't want to use VBA or named ranges, i.e. create an array of noncontiguous cells and evalute & sum them.
View Replies!
View Related
Find Highest Value In Noncontiguous Cells..
I have a cricket excel sheet that contains batsmans scores over a season. These scores are kept in noncontiguous cells for each game (ie. D5, J5, P5, V5 etc..) In the cell next to the score is an option for the user to enter an '*' to denote a not out score (these are in E5, K5, Q5, W5 etc.) I can sort out a formula to find the highest score (from D5, J5, P5, V5) and place this high score in a cell elsewhere  but what I really need to do is to check if the high score is not out by looking to see if there is an asterisk in the adjacent cell, and then place the score AND asterisk in another cell.
View Replies!
View Related
Copy NonContiguous Cells In Selection
I have a spreadsheet named PRODUCTS and in row A there are a bunch of product codes. I would like for the user to be able to use the CTRL and mouse to select all the products that they want and then once they push that button I would like it to paste the user selected cells into a sheet called Quote starting at A7.
View Replies!
View Related
Average NonContiguous Cells & Multiply
I've tried hard to come up with a title as accurate as possible. Here is my problem: I have six cells that need to be summed (N9,Q9,T9,W9,Z9,AC9). The sum of the cells needs to be multiplied by however many cells that have numerical data in them divided by 6. Assuming all cells in the range (N9,Q9,T9,W9,Z9,AC9) have the value of 50 the following rules would apply: ...
View Replies!
View Related
Check Non Contiguous Range To See If All Cells Are Filled
Is there a way to prevent a workbook from closing or being submitted until information has been entered into the following cells? B78, B80, B82, B84, B86, B88, B90, B92, B94, B96, B98, B100, B102, B104, and B106?
View Replies!
View Related
Check For Empty Cells In Non Contiguous Range
The application reads in a file, whcih can have various formats. To check which format it's in, I plan to look for certain empty fields/ cells. I can successfully detect a group of empty cells by explicitly testing each one, but when I put them all in a range and test that, the check fails. So far I've reduced the problem to the following example code. Sub check_clear() Range("g1:g8").clear If IsEmpty(Range("a1")) Then Range("g2") = "A1 empty" End If If IsEmpty(Range("b1")) Then Range("g3") = "B1 empty" End If If IsEmpty(Range("c1")) Then Range("g4") = "C1 empty" End If If IsEmpty(Range("d1")) Then Range("g5") = "D1 empty" End If.............. The result is that each individual cell check results in the relevant "XX empty" message. However, the test that the range of multiple cells is empty never produces a result. I'd really like to understand the underlying reason  as well as find out how to perform an isEmpty test on a range. I'm looking more for guidance and insight than a canned solution
View Replies!
View Related
Average Noncontiguous Cells & Exclude Zeros
I would like to average a noncontinuous range of cells while also excluding all zeros. I am averaging hours worked and the hours are found in cells: B2, D2, F2, H2, J2, L2, N2 Some of these cells contain zeros at this time and I do not what to include the zeros in the average. I have found formulas that would work but they are all with continuous cell ranges.
View Replies!
View Related
Contiguous Vs. Noncontiguous Cells Used In A Function Call From VBA
I am working with several columns of data, and each column's range is stored in a range array. My program needs to use a specififc combination of the columns as the second argument sent to the regress function. In certain cases, the columns I am referencing are not contiguous, so the function won't work. Here's an example: Suppose we're working with 3 independent variables (a simple case) whose data is stored in three contiguous columns named x1, x2, and x3. The program I've written will call the regress function 6 times: once using x1, once using x2, once using x3, once using the union of x1 and x2, once using the union of x1 and x3, once using the union of x2 and x3, and once using the union of x1, x2, and x3. Suppose the range for x1 is d5:d18, the range for x2 is e5:e18, and the range for x3 is f5:f18. Also, each union of ranges is stored as one element in an array called Combos, which is sent to the regress function. The regress function returns an error when prompted to calculate regression stats for columns x1 and x3 because they aren't contiguous. I need to know if there is some sort of way around this. write a procedure that will detect if the ranges being input to the regress function are contiguous, and if they are not, copy them to a new worksheet so they will be contiguous and then send the new range references to the regress function for calculation purposes? Is there an easier way around this issue? If I can't get this to work, my entire program is wasted.
View Replies!
View Related
Counting Formula (compare Values In 2 Separate Columns To See How Many Times The Same Value Appears In Both Columns)
I'm trying to compare values in 2 separate columns to see how many times the same value appears in both columns. Ideally I would be able to insert a range function to compare the values in the column "ID 1" against the values in column "ID 2" and return the count of times that a value appears in both columns. For example 2122, 1112 and 1718 appear in both columns and I would like the formula to return a count of 3. ID 1ID 2 12342122 45671112 89101718 11122678 13144544 15162324 17189987 19201215 21221928 1976 2576 2345 4678 In my actual project I'm comparing 2 columns in the same worksheet. The column are column B with data in cells B2:B10266 against column C with data in cells C2:C18560.
View Replies!
View Related
Look Across Values In 12 Columns, Return The Number Of Unique Values
I have 12 columns of data. In those 12 columns of data I have 3 digit numeric IDs. I want to count the unique number of IDs for each row. I have about 14K rows. What would be the best way to do so? Some rules about how the data is stored. I have 12 columns of data with anywhere from 1 to 12 columns having data for each respondent. Data always fills left to right and never skips columns. I have attached an example file that represents how the data is stored and the output I would like (Unique Count).
View Replies!
View Related
Short List When Meet A Certain Criteria.
I need to extract key cell info from a master list when one of the cells meets a certain criteria. Example: Each row of the main sheet represents a Purchase Order with columns AM each containing text, numeric, or currency values regarding that PO. Column F represents a 4digit activity code that defines exactly what was purchased. If the activity code for a row is in the 1000 series (1000 1099) I need to assign info from 3 other columns (say B, E, &G) to a subsequent sheet for tracking. My limited Excelovision has built this: (sheet 1 represented as FY10) =IF(AND('FY10!F4>=1000,'FY10'!F4
View Replies!
View Related
Counting Even If Doesn't Meet Criteria
I am trying to use this function to figure out averages for tests. there can be up to 25 tests in the sheet which is where the "YES" comes in. If the test booklet was used for this class then YES will be placed in the cell in field B2:B26. Now thes issue I'm having is that the formula is doing the math for the fields C2:C26 if there is a number in them if there is even if it does not say YES in the B2:B26 field. Here is the formula/function I'm using: =(COUNTA(C2:C26))/COUNTIF($B$2:$B$26,"YES")
View Replies!
View Related
Sum Rows In Column That Meet A Criteria
I have a filtered list of data expanded to "all" in each column. One column is "percentile" (25%, 50%, 75%, etc). Search down the "percentile" column until you encounter 25% as an entry, then sum a number in the same row from a different column with each 25% entry you find.
View Replies!
View Related
Summary Count That Meet Criteria
In my spreadsheet I have several columns and I have written a formula that has two conditions. If these are met the result is counted. However I want to add another condition. I need to write a formula that displays information relating to the conditions in a cell if the other two conditions are met. For Example, if the formula picks out that a row has the word 'John' and has the a number between 1 and 14 it will copy the inputted data at the beginning of the row.
View Replies!
View Related
