# Sum Cells Which Meet Certain Values In Non-contiguous Columns

Jan 1, 2009I'm trying to sum cells which meet certain values in non-contiguous columns. It's difficult to explain so I've attached a sample wookbook.

I'm trying to sum cells which meet certain values in non-contiguous columns. It's difficult to explain so I've attached a sample wookbook.

I have some data like the following:

2 3 4 8 4 1 3 3 1 8 1 3 5 8 1 2 5 6 1 7 9 3 3

I would like to be able to count the number of times 1 follows 8 for example. For the data above the answer is 2.

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 3 Replies View RelatedBasically, need to count rows that have 5 columns of data in it. If all 5 columns within a row have something in them greater than one character, that row needs to be counted.

View 1 Replies View RelatedBP Formula.xlsxI'm not sure what formula to use or if Excel can do this. I want to use it to determine number of blood pressures in a certain range. So if I use column A for SBP (systolic BP) and column B for DBP (diastolic BP). I want a formula to tell me if column A is between 140-159 or column B is between 90-99. I can get that part, but what I'm having trouble with is it counting each row twice if both column A and B meet the criteria, whereas I want every row (person) to only be counted once if either column A OR column B meet the criteria. (See attachment)

View 5 Replies View RelatedI would like to pull all rows and columns from a table and copy them to a separate sheet. I would like to pull the data if a certain value is present in a particular column. Essentially, it is the same effect of filtering the data but I don't want to have to keep filtering and copying specific cells and pasting them into a different sheet if I need to change the raw data.

Example: I would like to copy all ids, store, and partner information IF the partner is "X". I would like to put this information in Sheet A.

Capture.PNG

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.)

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 non-sequential 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.

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.

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"

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.

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

I have a range of data and need to summarize some of the data. In the table below is an example of the data. I would like to have a single formula where I can count the number of rows where the end date - start date is < 14 days. I would prefer not to add an additional column for the datediff value. Is there a way to do this with either arrays or named ranges?

Name

Start

End

Other data

Verizon

1/10/2010

1/25/2010

AT&T

3/2/2010

4/14/2010

I have a table that I would like to search to return all the values that meet 2 criteria entered by the user.

I have 3 columns - Role Name, Skill, and Skill Level - I'd like to be able to enter the skill and skill level and return all the roles that meet the two entered criteria.

At the moment I have an array, but it only returns the first value from the Role Name column that it finds.

=INDEX(A2:A100,MATCH(1,IF(C2:C100=G4,IF(E2:E100=H4,1))),0)

Where the A column is the role name, C is the Skill, and E is the Skill level. In cell G4 the user enters the skill to be search, and in H4 the level required for the skill (a scale of 1-4)

Is it possible for the formula to return all the values in column A that meet the criteria entered in G4 and H4?

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 a1-b1 to get the number of days, multiplied by costs. This particular spreadsheet is laid out in calender format, hence the problem)

I have a spreadsheet with 2 tabs:

AllData:

Name

Data

Read1

[Code]....

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:

1-3

25-36

39

42-44

48

62

65-72

75

80

103-106

141

In the formula I have numbered the results from 1 to 11, strictly for illustrative purposes. A return of X, 1, YES, etc.

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...

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

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

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 8 Replies View RelatedI have tried:

Code:

With Sheet1

x = .[b7:i16,b19:i23,b27:i32].Value

But that only loads the first range (.[b7:i16]).

This does work:

Code:

.[b7:i16,b19:i23,b27:i32].Copy .[z1]: x = .[z1:ag21]: .[z1:ag21].Clear

I would rather avoid having to copy/paste, load the array, then clear. Is there a way to load the array directly?

I am trying to copy paste info from one sheet to another, however, I would like to select where the information is to be pasted, not have it go to a predetermined cell or column.

Here is the code I have so far that works fine, but it requires a macro for each column I would like to paste to.....

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.............................

However I have a subject at uni that requires me to create a series of regression models, histograms, correlation matrices etc.

For part of the assignment, i have to run 4 regressions (one for men & one for women) with dependent variable as average wages, and independent as bfast 1,2,3 and dinner 1,2,3 (all of which are dummy variables) (0 for male and 1 for female).

The second two regressions are exactly the same, except average wages must be transformed into log which i have already done.

I dont know how to differentiate the regression models into male and female (if possible). We must also include residuals and residual plots.

I keep getting an error that says non-numeric data, the other says input range must be a contiguous reference.

I use to have a macro that could copy formulas from non contiguous ranges and paste the values in the same cells. So for example, I could select a non contiguous range like B5:B10, E10:F12, and G1:G10. The macro would then copy and paste the values in those 3 ranges, so those 3 ranges would be values instead of formulas.

I think it worked by storing each range and then looping through each range to copy and paste special 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 9 Replies View RelatedI 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 14 Replies View RelatedI 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 1-96) that has an Acid Number > 0.1 . Is there a formula to do this?

View 13 Replies View RelatedI need a formula that will tell me if EITHER two cells = a text word. I've tried a few things and can't seem to get it to work!!!!

See in the example of my spreadsheet below: If A2 = FALSE or B2 = FALSE then D2 should display "Allowed" if either are TRUE D2 should display "Not Allowed"

Is this possible!?!?! I've tried way to many different formulas and am close to giving up..

A

B

C

D

1

Not Slow

50%

FY

Change

[Code] ........

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

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

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: AB-12345, BP-12345), 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.

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 5 Replies View RelatedI have a rather large database that I'm trying to automate colours in, based on data that is inserted into Column 'D'. I've used the following code (with some success), but it colours columns that I don't want to colour:

VB:

Sub KeyCellsChanged()

Dim Cell As Object

For Each Cell In Range("D1:D5000")

[Code].....

That works fine - but the problem I have is that I only want to colour columns A:N, Q, T, V, AB:AE, etc. (random columns and not always together). I have already tried to replace parts (as follows), but get all sorts of errors (in particular Run-time error 1004):

VB:

If Cell = "Rabbit" Then

Cell.Range("A:N,Q, T, V, AB:AE").Interior.ColorIndex = 42

Is there someway that a line of code can determin which cells to colour in the row, or alternatively code that will colour the columns a specific colour and make them stay that colour when the first code above is used?

I can find the min value excluding zero in a range of contiguous cells, but how can I do it if the cells are A1, A3, A25, A67, etc?

View 9 Replies View RelatedCopying and pasting into non-contiguous cells.

I want to be able to filter my data set, so for example I can filter column A for the value Berkshire, I then want to be able to select all the Berkshire's in Column A (not a problem, simply select them and copy), but I then need to be able to paste them into column B, so they appear in exactly the same rows as they do in Column A, but this doesn't work, it pastes them into hidden cells as well.

I've tried the Alt + ; (Visible Cells Only) option, followed by copy and paste and this makes no difference.

I have over 20,000 rows of data to filter, then copy the relevant cells and move to the same row in another column and currently I'm having to move the cells one by one which is ludicrous.

How would I go about sorting a selection of non-contiguous cells?

View 5 Replies View RelatedAm looking for routine that can be used to delete the contents of cells from multiple, non-contiguous cells throughout a single worksheet. I would like to place a "button" or some form of toggle-switch on the worksheet that will clear all entries at once. I see a thread "Macro Deleting Wrong Cells" that references a VBA sub-routine called "DelCells". I happen to be running Excel 2007 and am still adjusting to the new menu "structure" :-)

View 5 Replies View RelatedColumn b in sample is conditionally formatted based on it's values. I want to also apply that same formatting to the person's name in the chart in D2:I9. For example, Jeff is in bottom 50% so cell B2 is shaded red with red text. I would like to apply that same red shade and red text to all the cells in my chart that say Jeff. Also, as example, all of the cells in my chart that say Kelsey would be formatted with green shade/green text and so on...

View 1 Replies View RelatedI 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"))

ADVERTISEMENT