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:
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
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
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
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
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
Looking Up A Sum Of Data In Several Columns For Specific Values Of Another Column
=(SUMIF($J:$J, "Player1",$K:$K)+SUMIF($L:$L,"Player1",$M:$M)+SUMIF($N:$N,"Player1",$O:$O))SUM($I:$I) This looks up if Player 1 was in the top 3 scoring zones and adds up the players total score. I have another column in this table of data labelled 'Number of Players'. I was wondering how to lookup the total score for this player in the games he played under 6 players for example and the games he played in where there were other six players. I've tried using that formula ^^ with variations of IF and SUMIF formulas to try and figure it out, but I keep getting formula errors or it just returns with the total score, and not just with the total score under 6 players. I am also trying to create a graph of date on the x axis against score to date on the y axis  everytime I try, the x axis is always numbers instead of the date?
View Replies!
View Related
Sum The Values In Cells Based On The Values Other Column
I want to sum the values in cells E2:P110 based on the values column D. The values in D are formulas resulting in something that appears to match D112 in some cases. I'm using the following equation: =SUMIF(D2:D110,D112,E2:P110) My problem is that D2 :D10 have a formula in it and it's not matching. If I enter the result of the formula, all is good. How should I deal with this?
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
Sum Of 3rd Column Values Based On Nested If On 2 Other Columns
I have three columns as follows: ItemsWeekEnding Count a14/7/2007 10 a34/7/2007 3 a14/14/2007 11 a14/7/2007 5 a44/7/2007 6 a14/21/20078 a44/14/20072 a34/21/20071 a24/7/2007 6 a14/28/20077 a24/21/20074 a94/28/20079 I want to Sum Count values (column 3) for matching WeekEnding (Column 2) and Items (Column 1), example: a1 4/7/2007 15 as a1 for this weekend is occuring twice. I need the formula to do it in excel.
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
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
Find Cells That Sum Up To Given Values
I have a coloumn (i.e. A) with hundreds of numbers. I'd like to be able to write a number in a cell (i.e. B4), and have Excel find and tell the coordinates of those cells in coloumn A that sum up to the result I wrote in B4. I also would like to be able to "hit a key" and see the next possible set of resultcells.
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
Find, Meet Condition, Sum Range, Deduce, Find Next
i have spent 40 hours, and still didn't find a solution. Please help is need it!! example: i have to find all articles with same code (222). first one has Q =100 second one Q=250. soled Q=150 (i am talking about 5000 rows, 400 different or same articles per month 12 months) columnAcolumnBcolumnC 222 1000 11150 33370 222200200 333 and so on first i have to deduct from the first one it finds (max. till 0 ...it can not be negative) ....after finding another one it deducts the restit means 50 Is there any kind of formula with this possibility. if it is poorly writen please let me know for more info. i am not an expert in excell, but i have tried variations of sumif, vlookup functions, but i always get stuck deducting the whole Quantity from all of the same (222) articles .
View Replies!
View Related
How Do I Sum Up Values Only In Cells That Are Color Filled
This thing drives me crazy for the past few days. Please helpI can go to sleep. I have several columns with numerical data. When certain criteria are met, a person manually makes some of the column fields a green fill color via the color fill button. Let's say I have data in cells E4 to E14 Below, I have a total field (auto Sum function used to total all), And, another row with The Colored Green totals. How do I enter the appropriate code to total only the fields that are green? I have found some info on verious sites but I have been unsuccesful to implement. [url] I attach a sample sheet, I was playing around a bit, you can ignore the fill color red, just deal with the green fields.
View Replies!
View Related
Formula To Sum Values Based On 2 Cells
I have three columns of data. I want to give the average sales per person per year but I don't know how to combine two columns in the formula. ManagerYearSales Sarah2006200 Sally2006150 Stuart20063000 Steve20064000 Sarah2005100 Sally2005120 Stuart20054500 Steve20055000 I want the output to be something like: Average Sales Per Year 20052006 Sarahxxxxxxxx Sallyxxxxxxxx Stuartxxxxxxxx Stevexxxxxxxx For example, I want to give 2006 sales for Sarah. I know that I could add an extra column to my first table which concatenates the Manager and Year. HOWEVER, In my example it would be fine but in my sales report there is a lot more data with a lot more going on. I don't want to have additional hidden columns that people might stumble upon and change). I want to use formula and NOT code as other people will be using the sheet and making changes to it. I want them to simply be able to autofill any additional rows rather than me having to update code. I have attached my example.
View Replies!
View Related
Add The Sum Of Cells By Color With Different Values
I have this fantasy footy comp I run and every week I enter player by player scores. To make it easier for me I need to a strange formula. Basically can you make a formulua to sum cell based on text colour? Every week the 3 players with the highest scores get votes. Eg highest gets 3 votes, second highest 2 votes and 3rd highest 1 vote. After each game I look thorough the players and change the colour of the 3 best to make them stand out. Green on black for highest, yellow on black for 2nd highest and red on black for third highest. Is there a way to at the end of each week to have a the cell (for example cell v43 in my sample) to update or add up all the 3pts 2pts and 1pts that player has accumulated throughout the season?? In my example I have just hand counted them and inputed them.
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
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
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 & 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
