# Sum Values In Column If Condition Met In Another

Feb 5, 2008
If possible, I would love for the answer to be a "formula" and not a macro, but if it must be a macro, then that's doable too:

Basically, I would love a formula that would add all the numeric values in "Column A", as long as "Column B" in that same row="YES".

So...

4 YES

6 YES

7 NO

9 YES

would mean 4 + 6 + 9 = 19

This will cover a variable amount of rows, but it will definitely be less than a few hundred.

View 2 Replies
ADVERTISEMENT
Jan 15, 2014

Here's a simplified example:

ColA

ColB

ColC

Row1

A

Y

A

Row2

B

N

D

[Code] .........

I'm looking to return the values in column A adjacent to the cells in Column B equal to "Y". The kicker has been returning only the cells where the condition is true. Column C displays the desired behavior.

The closest I've been able to get is with a simple IF statement but I'm pretty sure the answer is a far cry away from there and likely requires an array formula. I'd prefer not to use VLOOKUP or OFFSET but will if the alternative is very complex.

View 7 Replies
View Related
Oct 7, 2007

II want to sum the values under 'amount' column only if the corresponding value under 'Balance' column is "Paid"

NameAmount Start Date End Date No.of DaysInteresetBalance

Sat1 250 1-Aug-07 20-Aug-07 19 - "paid"

Sat2 550 4-Aug-07 20-Aug-07 16 -

Sat3 250 10-Aug-07 20-Aug-07 10 - "paid"

Sat4 450 6-Aug-07 20-Aug-07 14 -

Total PAIDAMOUNT(B1:B4)

For the above, the sum value should be 500. I have written the following function to calculate the same. On uncommenting the line 'paid = CStr(paidvar)' the value is displayed as '#VALUE!'. On uncommenting all commented lines, the value is displayed as 'Paid'.

Function PAIDAMOUNT(amountRange As Range) As Variant

Dim count As Long

Dim paidvar As Variant

Dim paid As String

Application.Volatile True

PAIDAMOUNT = 0

For count = 1 To amountRange.Cells.count

paidvar = amountRange.Offset(count - 1, 7 - amountRange.Column).Value

'paid = CStr(paidvar).................

View 2 Replies
View Related
Aug 21, 2014

Formula which will count unique values in column A based on condition in Column B which "y" .....

Show ranges as A:A instead of A1:A100 as I dont know the size of the table, it can be thousands rows .....

View 3 Replies
View Related
Oct 2, 2008

I'm trying to figure out if there is a formula I could use that will calculate the average of a group cells in one column based on the condition of another column. It's hard to explain, so I will show an example. All the data is on a one worksheet and I'm trying to show totals and averages on another worksheet. Location, Days

17, 4

17, 3

17, 5

26, 4

26, 8

26, 10

26, 7

On a different worksheet I would want to know what the average days are for each location. So is there a formula that I could use that will look at column A for a specified location number and then average all the days in column B for that location? I'm using Excel 2003 and have tried using the Average(if) but with no success.

View 2 Replies
View Related
Sep 7, 2009

I am wanting to paste formula from N1 till N X (X = varaiable row) where X reliant on Column A Row X. If Column A Row X has character "=====" it should paste N1 Formula all the way down till N Row X which is equal to A Row X containing "=====" .

I might be sounding complicated over here but it is a simple equation.. I have tried to approach this in the capacity i could by condition if Column A row x is blank delete the row.

View 2 Replies
View Related
Apr 20, 2009

I need a macro to match the row values on condotion.

To match the Q column values with S column values.

If suppose i have data i lke this then,

Q| R |S

1|22 yes 22

2|33 no 38

3|35 no 42

4|38 no 45

5|42 no 50

6|45 no

7|50 no

then the output should be like

Q| R |S

1|22 yes 22

2|33 no

3|35 no

4|38 yes 38

5|42 yes 42

6|45 yes 45

7|50 yes 50

View 9 Replies
View Related
Apr 4, 2014

I need a macro I can run that will insert a row whenever Column A changes (from PPN A to PPN B to PPN C, etc.) and that will insert string values into the Cells as follows:

In all cases, the insert cell values into column A (PPN) will be the value from the previous cell and column B (CPN) will be 'LABOR'

Here's image of before and desired after:

Before:

PPNCPN

4AQ02SLAG

4AQ02SAND

4AQ02CHIPS

4AQ02PORTLAND

4AQ02ADMIX

4AQ02AUTUMN BLEND

4AQ02OVERHEAD

4AQ03SLAG

4AQ03SAND

4AQ03CHIPS

4AQ03PORTLAND

4AQ03ADMIX

4AQ03BETHEL BLEND

4AQ03OVERHEAD

After:

PPN CPN

4AQ02SLAG

4AQ02SAND

4AQ02CHIPS

4AQ02PORTLAND

4AQ02ADMIX

4AQ02AUTUMN BLEND

4AQ02OVERHEAD

4AQ02LABOR -insert row and populate cells here

4AQ03SLAG

4AQ03SAND

4AQ03CHIPS

4AQ03PORTLAND

4AQ03ADMIX

4AQ03BETHEL BLEND

4AQ03OVERHEAD

4AQ03LABOR -insert row and populate cells here

View 1 Replies
View Related
Jun 28, 2007

I'm looking for some help. I need a function that can check if each of two seperate values is less than one. If less than one then divide by 2. Then give me the average of these two values. So it could be that it takes 0.5/2=0.25 for one value and 3 for another to give me the average of 1.625.

View 9 Replies
View Related
Oct 30, 2008

I want 3 extra columns in the attached sheet which is part of a 62000 row sheet.

column d..I want a formula for the lowest ip number(COLUMN C) in each event( event number in column1) printed on all rows in column d fo that event...

column e...second lowest ip number for each event..

coolumn f..third lowest ip number for each event..

View 4 Replies
View Related
Jan 26, 2010

I have a spreadsheet with employees performance results for the last 12 months. Column A contains name, columns B to M contain text "Gold", "silver" or "bronze". I need to count how many consecutive months thay have achieved "Gold" status BUT must include latest month.

So, if column M contains "silver" or "bronze", the consecutive answer (column N) would be 0. If col M = "Gold" but L doesn't then N = 1. If M and L = "Gold" then N = 2 etc etc.

Is there a formula I can use in column N for this without doing 12 "if" functions?

I think this needs to be an array formula but the ones I've tried I can't get to work.

View 6 Replies
View Related
Feb 6, 2008

I need a formula that will pull specified information from sheet2 (without having to sort) into sheet 1 by looking up the specified name. See attachd file.

View 3 Replies
View Related
Feb 12, 2009

I am having problems with choosing multiple values for the second condition in the b range. The formula works for one account in the array, but not multiples.

=SUMPRODUCT((('Data Sheet'!$A$2:$A$997<2110000)+('Data Sheet'!$A$2:$A$997>2119999)),--('Data Sheet'!$B$2:$B$997={"300100","302100"}),'Data Sheet'!D$2:D$997)

View 3 Replies
View Related
Jun 10, 2014

I've created the attached and want to get the top 10 values from column E of the INPUT sheet when a different name is selected in the drop down list in D1 on the summary sheet. I also want the relevetnt house number, Street name and Pososte code to populate I want to do this using VBA. How would I do this. top%2h010(1).xlsm

View 7 Replies
View Related
Aug 21, 2014

I have a list of product numbers and product prices (table 1).

I have a list of those (old) product numbers becoming new ones (table 2).

BUT some of those products have been merged together so multiple old product numbers have become one new one.

Now I have a list of the new product numbers and I want to calculate the price of the new product by adding up the price of the old products.

View 4 Replies
View Related
Feb 11, 2010

I'm trying to sum across multiple worksheets based on a simple condition. I have a time sheet with a worksheet for each week (52 worksheets). I've gone back and added a column to code the type of work done, types 1 - 17. I want to sum the hours of each type across all worksheets. This is what I have that doesn't work:

View 10 Replies
View Related
May 16, 2012

create a macro to perform paste special as value function. First scenario.. IF cell A2 is equal to 1 copy paste special values in I2:J2 cells ( this cells are formula.. need to perform paste special to prevent it in updating)then IF A3 is equal to 2 copy paste special values in I3:J3 cells.same action till A31. Second scenario is if O2 cell has a value of 100% copy paste special as values formula in K2 cell, then if O3 cell has a value of 100% copy paste special as values formula in K3 cell.. same action till O31 cell. take note I manually input values in A2 TO A31 cells as well as on O2 to O31 cells. I want the macro to perform the paste special as values only when I put value in Cell A. and Cell O (CELL A and CELL O are not dependent to each other so they should function individually in the macro).

View 8 Replies
View Related
Jun 17, 2012

I'm looking to get a formula to counting unique values listed in a column depending on a condition also find the attached file for more details

[URL]

View 9 Replies
View Related
Aug 13, 2012

I need to count the amount of unique values in a range, but only count unique values that also have a seperate condition. To illustrate with the 2 column dataset below....

Colour Letter

Yellow A

Yellow A

Green B

Yellow B

Blue A

Blue A

Yellow C

For this set of data i want 3 seperate cells for each 'Letter' telling me that, for 'A', the answer is 2 unique values (i.e. Yellow and Blue). For 'B' there is also 2 unique values (i.e. Green and Yellow) and finally for 'C' there is just 1 unique value (i.e. yellow).

View 2 Replies
View Related
Jul 19, 2007

I want use the formula sumif...something like that! =SUMIF(I77:I176;AND(>F1092;<F1091);Q77:Q176). I want sum the values between q77:Q176 if the value in i77:i176 is >F1092 and <F1091 But the multiple criteriun don´t work in the sumif.

View 8 Replies
View Related
Aug 21, 2007

I have a simple problem I am trying to find a set a values on a column and what to replace once found with the first 5 most left characters. Example:

in column A I can have text and numbers; the numbers that I want to find are in the following format xy:zw / xy:zw on each case the values can be from 0 to 9 so this is tons of posible combinations, I been trying to use **:** ? **:** but I don't get none. This is what I am doing

Set target = Sheets("Sheet1"). Range("A1:A9000")

For Each cell In target

If cell.Value = "**:** ? **:**" Then cell.Value = "00.00"

Next cell

A example would be 02:47 / 03:15 the next row could have 03:15 / 04:09

and so on however I could have text in some rows. In reality I would like to find and replace the cell.value with the Left 5 characters if the cell contains ANY value maching that format. Text in that column will never have that format combination

View 4 Replies
View Related
Nov 5, 2013

I have a listbox that lists some items from a spreadsheet, but what I cannot figure out is how to have the font color of the items be RED if the value if negative, and default BLACK when positive...

how to alter the code to accommodate this?

VB:

With UserForm1.ListBox4

.ColumnCount = 5

.ColumnWidths = "2 cm;3 cm;3 cm; 3 cm; 3 cm" [code].....

View 6 Replies
View Related
Mar 10, 2014

I am working on creating a simple Excel Database of an Engineering Tool Room Inventory for work. However I can not seem to get one particular 'NESTED IF Function' to do what I want it to do. (In advance warning, I may be going down the wrong route and might have used the wrong Formula in Excel but from my understanding the 'IF' function seemed the most plausible.

Issue: All I would like to do is for a NESTED IF Function to be able to say this:

1. When a user inputs data into one column, for example 'Column A', with the following data: 4" Square

2. Then the neighbouring column, for example 'Column B', would have a NESTED IF Function.

3. Which would output a logical response, "A, B, C, D, E, F etc...", depending on what is written in Column A.

If 'Column A' - 4" Square, Then 'Column B' - A

If 'Column A' - 5" Square, Then 'Column B' - B

& So on...

But the NESTED IF Function should only put a logical response if there is certain values within the cell, for example:

User types a value into 'Column A' which contain - 4 & S, Then 'Column B' - A

User types a value into 'Column B' which contain - 5 & S, Then 'Column B' - B

& So on...

Currently to do the above function, I have utilised the following Excel NESTED IF Function:

[Code] .....

& The following happens:

1. User inputs data into 'Column A'

2. The NESTED IF Function then provides an automatic response.

3. However it relies on the data being inputted without the ' " (Quotation Symbol)'

4. & With me defining the exact phrase that should be typed into the cell, for example, "4" Square"

Other: I have attached an example worksheet of the NESTED IF Function in practice, with two tables. One showing what is happening with the code above and one table showing what I would like to happen but with no function.

Example - IF Function.xlsx

View 3 Replies
View Related
Mar 25, 2014

Excel Version : MS Excel 2010

Attachment filename : <Forum to plot the values.xls>

My requirement is i want to plot the values V1, V2, V3 and V4 from the given condition (A1 / A2 / A3 / A4) and the given date.

If the given date is not available then the formula should take the older date than it not next date. For example if the given date is 10-Mar-14 and the date is not available for the given condition then it should take the older / earlier dates like 09-Mar-14 or 08-Mar-14 like and it should not take 11-Mar-14, 12-Mar-14.

The values should be plotted in the yellow cells.

View 1 Replies
View Related
May 10, 2013

Date Due

Date Ressolved

5/16/2013

5/17/2013

5/16/2013

5/9/2013

5/9/2013

How do I make this change, If the date resolved column has not data entry, but todays date is past the due date cloumn data entry change cell red and insert text to read "past date due"

View 6 Replies
View Related
May 9, 2014

I'm having a difficult time returning COUNTIF values for a positive match between 2 columns THAT meet a certain condition. Basically I want to iterate through column A sheet 1 (ONLY for values where column B is paid) and return a count for every instance there is a match of value A sheet 1 in column A sheet 2. In other words, I'm looking to find the number of ids marked as paid from Column A sheet 1 that exist in Column A sheet 2. I don't wish to return the actual ids, just the total count.

I've tried the following but I know there's an error in iterating through Column A the way I have it:

=COUNTIFS(Sheet1!$ColA:$ColA,Sheet2!$ColA:$ColA,Sheet1!$ColB:$ColB,"paid")

Sheet1:

ColA

ColB

123

paid

[Code] .....

Sheet2:

ColA

ColB

23

NY

[Code] .....

View 7 Replies
View Related
Sep 13, 2007

I have a spreadsheet and in the "I" column, it can read either "OPEN" r "CLOSED". If that reads "OPEN", I want the whole row to turn green, if "CLOSED" I want it to turn red. I have fiddled around with conditional formatting and can seem only to change that one cell!

View 4 Replies
View Related
Feb 6, 2014

I have been struggling with this formula for ages and have finally given up. What I would like to do is to do a lookup on the concatenated values of Province, Department and Initiative No, which has mutiple values in the status of milestone column, and then give the answer based on a condition.

The condition for this example should be, that if the returned status / or statuses of the initiative is all 1, then the value should be 1, if it's all 2, then the value should be 2, and so on.....but if the values returned from the status of the initiative is a combination of 1,2 or 3, then it should give me the answer 2. If 4 is part of the comination of values returned, then the value should be 4.

Zero - 0 should be excluded from the formula as it is part of planned values and not actual.

View 5 Replies
View Related
Oct 24, 2007

=SUBTOTAL(2,A2:A100) can be used in combination with any other formula so that when the data is filtered using autofilter it will only count values greater than zero.

I have attached a worksheet with the example.

In the attached workbook subtotal formulas are used in Row1 for sum and row 2 for count.

When the data is filtered using code "DUP" in column D, the subtotal in cell C2 is "4", whereas in the specified range there is a positive value in only 1 cell that is $1050 in cell C99.

Is there some combination that can be used with SUBTOTAL formula so that it will only count values greater than zero when data is filtered.

View 2 Replies
View Related
May 20, 2014

I am trying to calculate average for values in a range of data that fulfill a certain condition viz. >1000 &

View 5 Replies
View Related
Feb 25, 2014

How to get the cell value returned meeting condition both in row & column as well.

Sample sheet attached : Book1.xlsâ€Ž

View 3 Replies
View Related
Jun 10, 2014

i want to delete the column which are blank

e.g

i have data in Column A1:N1 then delete the column O:Z

View 2 Replies
View Related
Mar 15, 2009

I have a formula that tests the minimum time in a column. If the time is the minimum it gets a PB (Personal Best) notation. My problem is once a season there is a competion that needs to be recorded on the worksheet however I do not want this time for this competion counted in the MIN test or to get a PB allocated. I have worked out how to ignore the PB but I cannot get the MIN test to work properly. Dates of comp are in Col B, data (times) in Col C and formula for PB's in Col D. When a cell in Col B (Dates of comp) = Region I want the MIN test to ignore the value in the adjacent cell in Col C.

View 14 Replies
View Related
Mar 16, 2009

problem is in the same vain as my last thread "Formula to count MIN with condition". In the attached worksheet samples, Col B has competition dates, Col C is time/distance data, Col C (filled down) is the formula that recogises if the time/distance is a PB.

Once a season there is an event "REGION" where I want the formula to ignore the data in Col C adjacent to "REGION". The date of REGION changes every year and could be anywhere in Col B. The first attachment is what the sheet currently looks like and the second is what I want it to record, specifically D11. Even though D10 is a better distance, it would be ignored as it is REGION.

View 7 Replies
View Related
Aug 13, 2013

I need a formula to be placed in cell Col A, Row 1, that concatenates any and all cells in Col A, with a ';' separating each item. I only want this concatenation performed ONLY IF an adjacent cell in Col B contains the letter 'X.

Additionally, if the cell in Col A is null, then I the formula to ignore it, and not perform the concatenation.

Is there a way to do this formulaically?

View 1 Replies
View Related
Jun 17, 2008

I'm looking to create a macro that copies a row of data and pastes it within a range when a condition is met. For Example, if A1 of the header Row matches A2 within a Range of A2:A100, I want to Copy the A1 Row and Paste Special Values over the Match in A2. Likewise if the match is contained in Row 100, I would like to Copy from A1 and Paste Special Values into A100.

View 2 Replies
View Related
Aug 22, 2013

[URL]

I have Column C and D filled with numeric temperature and humidity data. I need next 16 rows (8 more set) to be filled with randomly generated dummy data by the values exist in C and D. Same numbers but randomly distributed.

View 2 Replies
View Related
Oct 1, 2013

There should be a formula to extract the largest values and its corresponding values. For example, if column

Products Costs

A1 Pen 200

A2 Pencil 125

A3 Radio 670

A4 Apple 1500

A5 Xbox 222

A6 TV 100

The desired outcome

Products Cost

Apple 1500

Radio 670

Xbox 222

Pen 200

Pencil 125

TV 100

The second goal is extracting the top 3 largest values using a formula.

Sample file is attached for your convenience. Desired outcome is in GREEN.

View 6 Replies
View Related
Dec 5, 2013

Below the two columns represent Column A (date and time) and Column B is temperature.

How can I take the mean of all temperature values for May 1, then take the mean of all temperature values for May 2, etc.?

5/1/2013 0:0012.4

5/1/2013 1:0012.4

5/1/2013 2:0012.5

5/1/2013 3:0012.5

5/1/2013 4:0012.5

5/1/2013 5:0012.4

5/1/2013 6:0012.4

View 2 Replies
View Related
Dec 24, 2013

I have two columns in excel, col A and col B.What I would want to achieve is following:

Col A Col B

A 2

A 2

A 2

B 3

B 3

B 3

B 3

C 3

C 3

C 3

Now I want a total of values in column B pertaining to unique (or say 1st occurences) of values in Col A. So in this case the output should be 8 ,Achieved as follows(2 +3+3).

[URL]

View 5 Replies
View Related