Sum Cells Which Meet Certain Values In Non-contiguous Columns

Jan 1, 2009

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.

ADVERTISEMENT

How To Count Contiguous Cells If Data Equals Values

Aug 13, 2014

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.

View 1 Replies View Related

SumIf Two Columns Meet Criteria

Nov 22, 2009

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 Related

Count Row If 5 Columns Within Row Meet Certain Criteria?

May 30, 2014

Basically, 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 Related

Formula For Counting Row Once When Two Columns Meet Criteria?

May 17, 2014

BP 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 Related

Pull All Rows And Columns That Meet Criteria?

Dec 22, 2013

I 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

View 2 Replies View Related

Macro: If Two Columns Meet Condtion Then Highlight Row

Apr 5, 2008

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 9 Replies View Related

Copy Non-Contiguous Columns

Sep 5, 2007

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.

View 4 Replies View Related

Sum Values That Meet Certain Criteria

Mar 25, 2009

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 9 Replies View Related

Delete Rows Where 2 Columns Meet Multiple Criteria

Aug 30, 2007

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 8 Replies View Related

Delete Rows Where Multiple Columns Meet Criteria

Apr 4, 2008

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 3 Replies View Related

Number Of Columns In Non-Contiguous Range

Jun 9, 2008

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 7 Replies View Related

Counting Number Of Rows Where Datediff Of Two Columns Meet Criteria

Aug 21, 2013

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

View 1 Replies View Related

How To Return All Corresponding Values That Meet 2 Criteria In Excel

Feb 14, 2014

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?

View 7 Replies View Related

Calculating On Values On Calender That Meet A Given Criteria

Nov 13, 2009

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)

View 5 Replies View Related

Scripting Dictionary - Compare Columns And Extract Data That Meet Criteria

Mar 23, 2014

I have a spreadsheet with 2 tabs:

AllData:
Name
Data
Read1

[Code]....

View 9 Replies View Related

Match Value To A Non-Contiguous List Of Values

Jan 18, 2010

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.

View 5 Replies View Related

Pass Values Between Two Non Contiguous Ranges

Nov 20, 2008

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 2 Replies View Related

Replace Different Values Within Non-Contiguous Ranges

May 8, 2008

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 2 Replies View Related

Replace Same Value Within Non-Contiguous Ranges With Different Values

May 18, 2008

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 2 Replies View Related

Grouping Contiguous List Into Non-Contiguous Groups

Sep 9, 2006

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 Related

Load Array With Values From Non-contiguous Ranges

Aug 2, 2014

I 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?

View 2 Replies View Related

Paste Non Contiguous Range As Values To Last Used Cell In Row

Feb 14, 2008

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

View 6 Replies View Related

Look For Cells That Meet 2 Criteria

Oct 6, 2006

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 2 Replies View Related

Regression Error - Non-Numeric Data / Contiguous And Complete Rows Or Columns

Apr 10, 2013

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.

View 1 Replies View Related

Copy And Paste Special Values For Non Contiguous Ranges?

Mar 18, 2014

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.

View 2 Replies View Related

Formula To Return True/False If A Row Has 6 Contiguous 0 Values

Sep 25, 2009

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 Related

Highlighting If A Range Of Cells Meet A Value

Jan 19, 2008

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 14 Replies View Related

Listing Cells That Meet A Criteria

Apr 11, 2008

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 1-96) that has an Acid Number > 0.1 . Is there a formula to do this?

View 13 Replies View Related

IF Statement - Two Cells To Meet One Criteria

Jun 18, 2013

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

View 6 Replies View Related

Colour Any Cells That Meet The Criteria

Apr 6, 2007

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 9 Replies View Related

Sum Range Where Corresponding Cells Meet Criteria

Jul 3, 2009

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 3 Replies View Related

Counting Cells If They Meet Multiple Criterias

Aug 16, 2008

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.

View 9 Replies View Related

Highlight Row If Cells In Row Meet Multiple Conditions

Mar 5, 2008

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 Related

Colouring Non-contiguous Cells With VBA

Dec 4, 2012

I 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?

View 3 Replies View Related

Min Value Excluding 0 For Non-contiguous Cells

Apr 2, 2009

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 Related

Paste Into Non-Contiguous Cells

Dec 18, 2012

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

View 1 Replies View Related

Sort Non-contiguous Cells

May 24, 2006

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

View 5 Replies View Related

Clear Non-contiguous Cells

Jul 23, 2007

Am 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 Related

Apply Conditional Formatting To Cells That Meet Certain Criteria?

May 19, 2014

Column 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 Related

How Do I Count Nonblank Cells That Meet Criteria In Another Cell?

Jul 19, 2006

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 14 Replies View Related

ADVERTISEMENT