Sum Values In Column Based On Condition In Another

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

Counting Unique Values Based On Condition In Different Column?

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

Return Values Of Adjacent Cells When Condition Is True Omitting Values Where Condition Is False?

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.

Calculate The Average Of A Group Cells In One Column Based On The Condition Of Another Column

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.

Insert Row And Values Based Upon Condition

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
4AQ02AUTUMN BLEND
4AQ03SLAG
4AQ03SAND
4AQ03CHIPS
4AQ03PORTLAND
4AQ03BETHEL BLEND

After:
PPN CPN
4AQ02SLAG
4AQ02SAND
4AQ02CHIPS
4AQ02PORTLAND
4AQ02AUTUMN BLEND
4AQ02LABOR -insert row and populate cells here
4AQ03SLAG
4AQ03SAND
4AQ03CHIPS
4AQ03PORTLAND
4AQ03BETHEL BLEND
4AQ03LABOR -insert row and populate cells here

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.

Condition Paste Formula In Column N Based On Column A

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.

Sum Values From Multiple Worksheets Based On A Condition

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:

Find & Replace Values Based On Condition

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

Font And Format Of Listbox Values Based On Condition?

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

Nested IF Function - Condition Is Based On Two Values Within A Cell

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

Condition Formatting Based On Dates And No Values Entered?

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"

Concatenate Column Based On Condition?

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?

VLOOKUP Multiple Values Then Give Answer Based On Condition?

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.

Copy Cell Value To Different Column Same Row Based On Condition?

Oct 13, 2012

I've schedule header date 1-oct,2-oct,3-oct.....etc and have two cells Last Date and Hours I need from those cells once i add date and hours to copy the hours and paste in schedule header in the exact date..

for example
#
Last date
Last Hours

[Code]....

but was too slow takes long time.

2nd VBA code to copy last hours to schedule date based on condition on last date cell.

Populate One Column Based On Condition Of Two Other Columns

Jan 17, 2014

15.png
Attached is the file & snap shot for the problem.

TABLE A
[table="width: 500, class: grid"]
[tr]
[td]Catagories state names devices
A4-100HP A ALT

[Code] .....

TABLE B
Catagoriesstate names devices
A4-100HP
A4-101MP
A4-102AP

[Code] ....

In above condition we want to have an automatic filling up of data in table B in column (name & device) but it should match first with state name with category codes of table B & then same with table A in order to avoid any wrong entry because in table A certain states are repeated but codes are unique so we want that filter should match two column of each table before filling data in table B.

Finding The Maximum Value In A Column Based On A Condition

Nov 7, 2008

This should be simple to do but I can't figure it out. I have a database that lists operating room numbers in one column and the length of the surgeries performed in those rooms in another column.

I need a formula that will give me the longest OR time for a given room. For example the room numbers are in column A and the OR times are in Column B. I've tried something like

Selecting Rows Based On A Column Condition

Jun 4, 2009

I have a worksheet which is created monthly by one of our company's employees which reports results for that months operations and successful audits. These audits are separated into various service lines and on the report which is submitted it takes on the following format:

1|Service Line|Data A|Data B|Etc.
2|___CR_______| Data | Data | Etc.
3|____________| Data | Data | Etc.
4|____________| Data | Data | Etc.
5|____________| Data | Data | Etc.
6|___DD_______| Data | Data | Etc.
7|____________| Data | Data | Etc.
8|___MS_______| Data | Data | Etc.
9|____________| Data | Data | Etc.

What I want to do is use a macro from another sheet that has the effect of: While ServiceLine = "CR" copy the row of that line and move it to my monthly summary workbook, sheet 1. Then when the Service line changes to DD have the macro copy that information to the monthly summary workbook sheet 2, and so on. The problem is, if I test the Service Line column each time it will be an empty string the majority of the time and I'm not sure how to combat that with my If, Do, For, and While statements. As I've thought about it there are two solutions that I can think of: someone knows of a way to test the row only if it has information in it, or if there is a way that I can copy the service line information down until the change in service line so I can test Service Line on each Row.

Filling Column Data Based On Condition

Nov 20, 2012

I have an excel sheet with 2 tabs. 1st tab provides the data for downtime of a manufacturing line. The last column (shift) is blank and needs to be filled based in shift schedule in tab2

On the 2nd tab I have the shift schedule, which tell which shift is working on particular days

Month
Date
7:00am- 7:00 pm
7:00pm - 7.00am

Feb
1
Shift A
Shift C

[Code] ........

Is it possible to write a macros that will look at the Date and time from tab 1 and assign the correct shift number in the shift column. For example the 1st row of tab 1 reads (Month=Feb, Date = 3:55:59 AM) so according to shift schedule it would fill C shift.

I have lots of data and to assign every event to shift # manually is not possible. So i wanted to know if there is a macros i can use to make life easier and learn at the same time

Exclude Rows Based On Condition In 1 Column

Feb 7, 2009

I have a worksheet that I want to export to a csv file. However, there are multiple rows that I want excluded in the export. These lines are recognized by the word "No" in column E. Is this possible?

Match Column And Colour Based On Condition

Feb 22, 2007

i am trying to do is match 2 columns data based on a condition ie i have data in column "b" of sheet called "balance" data is variable this column has unique indentifiers i want to look at another column and match the uniques identifiers in another sheet if the cell value in column "D" = "please investigate" otherwise do nothing, the (column f) it will be looking up too is in sheet called "hi- port" so if cell value in column "d" of sheet"balace"= "please investigate" match values that are same from colum "b" of sheet "balance" to that of column (f) sheet called "hi-port"

i would like to colour only entire row of sheet called "hi-port" when the data matches to other sheet ie from columns "f " too " b". colour can be any colour
igonre my ranges they are wrong just used as example

Sub start()
Dim oWs As Worksheet
Dim oRngCheck As Range
Dim oCl As Range
Dim cCola As Range
Dim cColb As Range
Dim rngIRd As Range
Dim rngACs As Range
Dim Match As Boolean
Set rngIRd = Range("a1", Range("a65536").End(xlUp)) 'define the rang
Set rngACs = Range("b1", Range("a65536").End(xlUp)) 'define the rang
Set oWs = Worksheets("balance") 'change to suit
'starts at b2 to llow for header row.......................

Delete Rows Based On Condition Of 1 Column

Oct 19, 2007

I would like to Use my Excel VBA program to search each row in a csv document for a name (located in a cell ) if the name exists then I would like to delete the entire row.

Whenever I try to do the above in excel, even when I save in csv format the file formating or something gets changed and the file which has to be procesed through another program then process incorectly.

Is there a way to do what I need while retaining the formating which must obviously be changed when the file is opened in excel?

Vlookup Using 2 Condition One Based On Devices And Grouping Column?

May 27, 2014

Attach below are 2 sample file and inside it consist of device,grouping and lb1,tb1,ab1. How do I use excel formulae based on 2 condition and set from vlookup with 2 condition file to vlookup with 2 condition 1 file like a vlookup.

Copy Multiple Cells Based On Condition In Column

Feb 13, 2008

I need the macro that looks in to say Column H sheet1, if value ="true" then copy cells in column A, B, D, F to Sheet2.

Macro To Segregate One Column List Into Three Columns Based On Condition

Nov 21, 2012

Lets say we have data in Column A, B, C and D and no row left blank. In the column A no cell left blank however in B, C, D any one cell only contain a value in that row. If B10 has any value in it then C10 and D10 are left blank (not empty). I want to segregate the data in Column A based on the value in B, C or D. So this one column data ( that is Column A) will split into three column. this segregated data to be put in E, F and G.

Wherever Column B has any value that's greater than zero content from the column A from the same row should copy to the E, Wherever Column C has any value that's greater than zero content from the column A from the same row should copy to the F, Wherever Column C has any value that's greater than zero content from the column A from the same row should copy to the G.

Calculate Mean Of Column B Values Based On Date Values In Column A?

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

Counting Values Of One Column Based On The Uniqueness Of Values In Another Column

Jun 11, 2014

Here is a table

Col1 Col2 Col3 Col4
8821015m14:44.0E__uMail
9266321m34:13.0E__uMail
92677165m23:43.0E__uMail
9309415m07:24.0E__uMail

[Code] .....

Here is some context

Each one of these rows represents an entry into a ticket in our incident management system. Col4 represents the method by which the incident was reported to us (Email, Voicemail, Walk In, Ask IT). A ticket can be open and closed with a single entry if the issue is easily solved or it can have many entries if the issue is complex and requires more troubleshooting or escalation to a higher tier of support. I need to determine the number of unique tickets submitted by each reporting avenue (Email, Voicemail, Walk In, Ask IT).

In this table the number of tickets that were:

submitted via Email is 14
submitted via Voicemail is 1
submitted via Walk In is 2
submitted via Ask IT is 1

Col1 Col2 Col3 Col4
8821015m14:44.0E__uMail = 1
9266321m34:13.0E__uMail = 2
92677165m23:43.0E__uMail = 3
9309415m07:24.0E__uMail = 4

[Code] ....

What I need is a formula that will produce this result.

Count Unique Values In Column Based On Values In Other Columns

Mar 14, 2014

I'm running into an issue trying to calculate unique values in a Data column based on a few variables in other columns.

My current formula in Summary tab D4:D19 is
{=SUM(IF(FREQUENCY(IF(Data!\$I\$3:\$I\$66<\$E\$1,IF(Data!\$A\$3:\$A\$66=\$H\$1,
IF(Data!\$C\$3:\$C\$66=A4,ROW(Data!\$I\$3:\$I\$66)))),ROW(Data!\$I\$3:\$I\$66)),1))
+
SUM(IF(FREQUENCY(IF(Data!\$I\$3:\$I\$66<\$E\$1,IF(Data!\$A\$3:\$A\$66=\$I\$1,
IF(Data!\$C\$3:\$C\$66=A4,ROW(Data!\$I\$3:\$I\$66)))),ROW(Data!\$I\$3:\$I\$66)),1))}

This is currently counting the number of times a date value (data column I) appears for that name (A4:A19) in the data when meeting all of the conditions. I need it to instead count the number of times a unique date appears for that name with the additional conditions met (which all appear to work fine).

The results in the pink highlighted cells (Summary column D) should be:

Names starting with A - 3
All others - 2

I've left some other columns in the data with X's so that I can easily convert this back to my working spreadsheet.

Summing Values Of One Column Based On Values Of Another Column?

Feb 21, 2013

I am trying to sum certain values in one column based on the values of another column. I made a few tables, generated some random numbers, and then I started writing up an if-then statement to see if I could get what I am trying to do to work. However, I was having difficulty getting the program to run, because I was declaring more than one variable (I think).

So for instance, I want cells in column G to sum forces in column C when a joint in column E matches a joint in column A. Similarly, I am trying to do the same thing for columns F and B.

I have attached a picture, which shows what I would like for the program to do automatically (rather than me doing it all manually)! I didn't include the code I wrote, because it was so minimal

Attachment 51660

Filtering Values In One Column Based On Values In Another Column

Apr 7, 2014

I'm currently working on a spreadsheet that contains 30,000 leads and their respective companies and i need to filter out any leads that work for companies present in a different list of companies (1,834 companies). Basically, i need to filter column A so that it only contains companies present in column B. Is there a way to do this using the advanced filter?