Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Sum Values In Column If Condition Met In Another

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


7 NO

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 Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Sum Values In Column Based On Condition In Another
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 -


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
For count = 1 To amountRange.Cells.count
paidvar = amountRange.Offset(count - 1, 7 - amountRange.Column).Value
'paid = CStr(paidvar).................

View Replies!   View Related
Copy Rows Where Condition In 1 Column Is Met
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 Replies!   View Related
Color Whole Row If Condition Met In A Column
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 Replies!   View Related
How Can I SUM Values Only When Specific Text In Another Cell In The Row Is Met?
I'm creating 10 'categories' for Column "C" that I plan on using drop-downs to select from. Column "H" has a value tied to "C". What I want to do, is have Excel SUM all of the values in Column "H" where the Category in "C" is a specific text value (e.g. say a category is "hardware", I want a cell with a formula that sums all of the values in "H" where "C" is "hardware".)

View Replies!   View Related
Sum Column If Criteria In Other Columns Are Met
I am trying to create a formula that will sum up a value in one column if certain critieria residing in other columns are met. I have attached a sample and the criteria.

View Replies!   View Related
Sum Values From Multiple Worksheets Based On A Condition
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 Replies!   View Related
Multiple Condition SUMIF: Sum The Values Between Q77:Q176
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 Replies!   View Related
MsgBox If Condition Not Met
Hi, Please could someone help with the following code. I have 2 sheets 'sheet 1' and 'sheet 2'(hidden). Sheet 1 has a control button that takes you straight to sheet 2, however what I wish to achieve is that before sheet 2 is visible a message box appears if a condition is not met. Something like, if sheet 1 R29 is less than 5000 then msg box appears (I would need about 4 lines in the msg box), if ok'd then sheet 2 appears. If the condition is met i.e sheet 1 R29 = or is greater than 5000 then the msg box does not appear and takes the user to sheet 2.

View Replies!   View Related
Formatting Until A Condition Is Met
I have the following code which prints the number of time steps required and displays it as t0, t1, t2 etc how can I make all the numbers subscript? i.e until the number of time steps = the total time.

View Replies!   View Related
Restart Sub When Condition Not Met
What can I do to have a sub only run when a random number is above a certain value? I have tried this:

Sub Filter()

With Application
If Range("Count") >= 2 Then
.Calculation = xlManual
Else: Application.Goto Filter

End If
End With

End Sub

Am i using goto incorrectly or should I try some sort of loop?

View Replies!   View Related
Allow Close If Condition Met
I want to create a situation where certain conditions have to be met before a workbook is closed. I copied the following from an earlier thread, but cannot seem to get it to work.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Range("A1").Value <> "x" Then
MsgBox "Pls enter x in A1"
Cancel = True
End If
End Sub

If I use the Auto_Close as shown next, it works except the user gets a prompt to save the file and exit, which I don't want.

Sub Auto_Close()
If Range("A1").Value <> "x" Then
MsgBox "Pls enter x in A1"
End If
End Sub

View Replies!   View Related
Display N/A If Condition Not Met
I have an Excel sheet that I use for production, in one of the cells i have put a formula that displays the date of manufacture if a length of a roll is entered greater than 0, if no roll length is entered then the formula displays FALSE, what I want it to do is display nothing or N/A if no roll length is entered.

This formula is in cells G18 to G27, the formula that i am using at the moment is:
=IF(G18>0,(TODAY())) and so on down to cell G27, is this possible to do this?

View Replies!   View Related
Check If Condition Is Met
Private Sub cmdNext_Click()
' Save form contents before changing rows:
' check that it is not the last row - if it is then show message
' and do not go to next record:
If Sheets("Data"). Cells(lCurrentRow, 1).Value = "" Then
MsgBox "This is the last Output."
lCurrentRow = lCurrentRow - 1
Exit Sub ' Abort if no data:
End If...................

I'm having trouble with is the bit where it is supposed to ask for the txtAddInfo to be filled (if blank) and if the txtdifference is greater than 10% or less than -10%. It seems to work fine if the txtDifference is less than -10% but not if the txtDifference is greater than 10%. It just seems to get stuck, the message box is displayed even if txtAddInfo has infomation within it.

I'm not sure why as I use ..........

View Replies!   View Related
Column SUM Produces #N/A (Vlookup To Find Values That I Want To SUM)
I have a column using Vlookup to find values that I want to SUM.

Some of the look up values produce a #N/A and result in a total sum of #N/A.

How do I get the SUM of a column of numbers when all of the cell values are not in fact numbers.

View Replies!   View Related
Delete Rows If Condition Not Met
I want a macro to delete the entire row based on the value in Column Q ....

View Replies!   View Related
Copy Data If Condition Is Met
to copy the information from A3 to D3 in another workbook if in colum E "Delivered" is selected from the drop down menu, using vba code.

The thing is that drop down menus are dinamicly dependent. If "Expected" is
selected in E3 then F3 should be empty. If "Delivered" is selected then from
the menu in F3 i have the choise from, let say 20 clients

When I choose "Client1", for example, i wont the vba code to copy the information from A3:D3 range and paste it to another workbook, in the sheet "Client1"

I don't know where to start from building this code,

View Replies!   View Related
Disabling Macro When Condition Met
I have recorded a simple macro that refreshes a set of random data in my sheet everytime I click on the button I made.
When I see a zero in one of the cells I know to stop refreshing. The thing is, I click on the button quite rapidly and sometimes miss the zero. I need to click on this button quite quickly so slowing down isnt an option!
What I need is for excel to stop that button operation when it sees a zero in one of the cells.

View Replies!   View Related
Sound Alert IF Condition Not Met
I am trying achieve the following. I have bracketed each part seperate part for clarity.

(if a1+b1+c1 = 10 or more), and (a2 = 10 or more) and (b2 = 10 or more) (then sound an alert) (and display the number 1 in d1) (if not ignore).

I can do parts of the formula but i am getting very very confused trying to combine them together. I am trying to make this formula work in d1.

Can anyone tell me if this is a valid piece of logic that can be represented by an excel formula.

View Replies!   View Related
Exit Loop When Condition Met
My code is meant to ensure a string in a cell begins with three letters and ends with 5 numbers. It seems to work, but how can I exit the loop and go to the message box once the boolean variable is set to true? I was hoping to avoid using labels. Also, I would appreciate any suggestions in compacting the code if possible, but without using CreateObjects".

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("h17")) Is Nothing Then Exit Sub

Dim Ls As String
Dim i As Integer, z As Integer
Dim MyString As String
Dim MyClVal As String: MyClVal = Target.Value
Dim MyBL As Boolean: MyBL = False

View Replies!   View Related
Delete Row When Condition Met
In a worksheet I have a list starting Col A being the running reference no. such as Action 1, Action 2 and so on. Each time a new action is needed the numbering increased by 1. The next column is the description of the action. This list start at row 39 of this worksheet.

This is a protected worksheet and entry of data is limited to specify cell in the list.

1. Look for the last Action Item.
2. Check if the description column (Col B) is blank.
3. If Col B is blank delete the whole row.

View Replies!   View Related
Calculate Average If Condition Met
I have two columns of data. Column B is age column C is gender. I want to calculate average male age and average female age. Suggestions?

57 f
53 f
47 m
40 f
42 m

Average female age is ___
Average male age is ___

View Replies!   View Related
Auto Email If Condition Met
I can get it to work with 1 email and 1 condition but not 2.

Sub auto_open()
ThisWorkbook.Worksheets("Sheet1").OnEntry = "DidCellsChange"
End Sub

Sub DidCellsChange()
Dim KeyCells As String
KeyCells = "A1:A99"
If Not Application.Intersect(ActiveCell, Range(KeyCells)) _
Is Nothing Then KeyCellsChanged
End Sub ................................

View Replies!   View Related
Deleting Row If A Certain Condition Is Met
I have a list of data (this list is variable) and I want to delete the rows that have the word Revaluation in column L.

I tried the following but it did not work

Sub Delete()

Dim Lastrow As Long, i As Long
Lastrow = Cells(Rows.Count, "L").End(xlUp).Row
For i = Lastrow To 1 Step -1
If Range("L" & i).Value = Revaluation Then Rows(i).EntireRow.Delete
Next i
End Sub

View Replies!   View Related
Delete Rows When The Condition Is Met...
I'm working this spreadsheet and i want to automate of deleting the entire rows once the last two columns contains both Zero values. the worksheet cotains thousands of row to clean-up.

View Replies!   View Related
Mulitple Action When Condition Met
How do you set the code so that, if a condition is met, then several cells are filled with values?

So far, my code is

HTML If Cells(i, 2) < Cells(i, 4) Then
Cells(i, 5) = "A"
Cells(i, 6) = Cells(i, 2)
End If

View Replies!   View Related
Row Highlighted When A Condition Is Met
I want to highlight the row when a condition is met. As by using conditional formating only a single cell will be highlighted.

in the attached file,

I had used the condition in column H, when cell contains "BLI" the cell will be highlighted in Green, but I want to highlight the complete row with green color wherever Column H contains "BLI"

View Replies!   View Related
Get Macro To Run If Condition Met?
I have a macro in a file that I want to only be able to run if a certain cell in the worksheet says something in particular. I.e. This macro can only be run on Day 7 of any week. There is a cell on the worksheet that says "Day 7" or whichever day it is. I want to get my macro to run if that cell does say Day 7 and to come up with an error message box if it doesn't.

View Replies!   View Related
Select Case Condition Not Being Met
Select Case Condition Not Being Met

View Replies!   View Related
Recalculate Cell Only If Condition Is Met
to write a VBA code that will have a cell RE-calcuate an excel formula ONLY if a date i have in a cell to the left of it is greater than another cell in the worksheet.

These dates will change so i cannot have the value entered into the VBA code, but rather linked to the data i would enter into the said cell.

View Replies!   View Related
Leave Loop When Condition Met
I am writing a very simple Do While loop where VBA searches for the first nonempty cell in a column starting from cell C3. The error I get is "Overflow". I suspect that when all cells are empty it goes down the column till the last cell, which is around 65,000. How can I put a restriction on this loop? I need the search to be restricted to range C3:C95, because my data ends in row 95. In other words, the code essentially should do this: go from cell C3 to cell C95 and search for the first nonempy cell in this range. When you find the first nonempty cell, assign name "x" to this first nonempty cell.

View Replies!   View Related
Show Blank If Condition Met
I would appreciate it a lot if someone could explain to me how I hide the value in the cell which a result is stored in when one of the two cells in the calculation is empty.

For exaple:
C1 should be blank if A1 or B1 is empty in this formula:

View Replies!   View Related
Pop Up Message If Condition Is Not Met
i have a column(G) in a spreadsheet active sheet will do. i would like a message box to pop up if nothing is populated in the cell from range g1 - onwards...range does change otherwise keep processing code

View Replies!   View Related
Automatically Clear Rows When Condition Is Met
I've got a workbook (attached) with a "summary" sheet & 2 detail sheets for tracking vacation time used, and I need to make it automatically clear out an employee's "used" vacation hours automatically on their anniversary date.

Can anybody help me please? I've had no luck thus far & "the powers that be" are really getting on me for this now -

View Replies!   View Related
Update Cell From Another Sheet If Condition Met
I have two worksheets. one has a list of formulas which i manually enter or change as time goes on. the other spread sheet has the same list of formulas, but I just chose which of those formulas I want to use frmo the drop down list provided to me by using the data validation of the other page. Each row that I enter by selecting the correct drop down options, i would chose a product name alogn with it. so its either Service or Fix. The question i ahve is, is it possible to adjust the manually entered formula and only have the formula rows where the service words are in another column but same row updated?

View Replies!   View Related
Add Text To All Worksheets If Condition Met
I have a macro that is supposed to type a text in column 8 if condition is met, on all sheets but one (" Total"). When I run it, it stops at "Next Line" with a "Compile error: Next without For". For the life of me, I can't figure out where I'm missing a "For".

Sub ForceMatch()
Dim ws As Worksheet
Dim line As Integer
endline = Range("A1000").End(xlUp).Row

For Each ws In Worksheets
If ws.Name <> "Total" Then
With ws
For line = 3 To endline
If (Cells(line, 9).Value <= 5000 And Cells(line, 8).Value = "") Then
With Cells(line, 8).Value = "FORCE-MATCH CANDIDATE < $5K ICDP"
Next line
End With
End If
Next ws
End Sub

View Replies!   View Related
Stopping User Saving Unless Condition Met.
I am looking to stop the user saving the sheet if certain fields are not complete.

The sheet has headers on line 4 and runs from A4 to M500

In the M cells there is a counta function to check the records are complete.

M4:M500 shows either

Complete - All records comlpete
Incomplete - something missing
Blank - nothing entered yet.

What i want to do is, if one of the cells in M shows incomplete not allow them to save the sheet until its complete.

I tried the folowing (was my attempt to modify another post)

View Replies!   View Related
Play A Sound When Cell Condition Is Met
I have 2 cells that take imported info. from a DDE server. Lets say one cell currently has a number of 9.00. Lets say the 2nd cell has a number of 1.00. Lets say the 1st cell over time goes from 9.00 to 8.50 to 7.33 to 6.67 and so continues to go down. At the same time, lets say the 2nd cell is gaining and going from 1.00 to 1.33 to 2.00 to 2.67 to 3.33, and so on...and it continues to go up. I want to have a certain sound play once the two cell numbers "cross" each other. I only want this sound to play one time and not every "tick" as they cross and go up/down. Can someone show me how to program this?

View Replies!   View Related
Add A Time Increment When A Condition Is Met
I have two columns C & D. In col C (C10) a time is entered (either 13.45 or 0:33.22 format). If column D (D10) is blank then the time in Col C stays the same (13.45 or 0:33.22). However if Col D has a M in the cell (D10) I want to add 0.24 to the time in Col C (C10) eg 13.69 or 0:33.46. I have tried various formula combinations but keep getting circular reference errors.

View Replies!   View Related
Delete An Entire Row If Condition Is Not Met
I'm working on Outlook 2003 and Word 2000 which are not compatible. I have an
Excel Sheet which are my contacts from Outlook and I want to re-work it to
have only a certain category left.

I need a macro that does
- search a certain column
- deletes the row if it does NOT find a certain condition

Ideal would be a little pop up window to enter which category I want left.

View Replies!   View Related
Macro To Delete Rows If A Certain Condition Is Met
I want to create a macro the will delete all the rows in a sheet if 1 of 2 conditions are met. The first condition is if a cell is blank or if a cell contains the word "negative"

View Replies!   View Related
Run A Macro Automatically If Condition Is Met ..
How do I run a macro atomaticly if the value in the cell is > 3. That is what I have so far:

If Worksheets("Variables").Range("B23").Value > 3 Then

I do not know what I should have before that?

View Replies!   View Related
Prevent Save Option If Condition Not Met
I have a list dropdown from cells A2 to A10. If only when a user selects "orange", he has to key in some description in column B.

Eg if he selects orange in A2, he has to key in some description in B2.

Is it possible to set some formula or macro which prevents user to save this file if he leave blank in B2 or B10, depending on which row he has select "orange"?

View Replies!   View Related
Prevent Data Entry If Condition Met
I am adding up the value of a range of cells in one cell. What I want is If the value of the cell which is adding up the values of the range of cells exceeds a particular number it should throw an error.

View Replies!   View Related
Run OnTime Method If Condition Met
currently i have this:

Sub StartCycleB()
ActiveWorkbook.UpdateLink Name:="c:/a/b/c/test.xls", Type:=xlExcelLinks
RunWhen = Now + TimeSerial(0, 15, 0)
Application .OnTime RunWhen, "MyMacro", , True
End Sub

Works fine. Its updating and refreshing a file every 15 minutes. When a cell in the test.xls changes to TRUE, my sheet picks it up and changes to TRUE also.
So here is the problem: Once my sheet changes to TRUE, another macro activates in my sheet. This macro will automatically activate every 15 minutes if value is TRUE. BUT the problem is i ONLY WANT TO ACTIVATE THIS MACRO ONCE a day IF the value in my sheet is TRUE. So in summary how do i activate a macro based on a TRUE value on a cell, to happen ONCE a day and then stop?

View Replies!   View Related
Match Columns To Check If Condition Met
If you open the attachment.. then you will see that Column A has all the names and Column B has it's Value.. likewise.. Column D has all the names and Column C has it's values...I'm trying to first match the names from Column A to Column D and then see if Column B matches to Column C..

View Replies!   View Related
Prevent Click On Check Box If Condition Met
I have two check boxes in a worksheet created from the "Forms" toolbar.

Is it possible to prevent a user from clicking these two check boxes if he meet a condition in cell A1.

Eg, If cell A1 value is "Marketing", the user is not able to click on these two boxes.

View Replies!   View Related
Conditional Format To The Colour Of A Particular Cell If The Condition Is Met?
is there any way to set the conditional format to the colour of a particular cell if the condition is met?

View Replies!   View Related
Run Macro When Cell Condition/Criteria Met
Two ranges A1:A20 (input values) and B1:B20 (results).

Input values are always the digit 1 which starts the calculation and the result comes up in the cell to the right.

Input (1) in cell A1 gives a result value in cell B1.
Input (1) in cell A12 gives a result value in cell B12.
I need a macro “StartCalc” to do the following thing:
Go to cell A1.
Put in the value “1”
Check the result cell B1.
If “result value” > 20, then run macro “Test” (this macro is working)
Go to cell A2.
Put in the value “1”
Check the result cell B2.
If “result value” > 20, then run macro “Test” (this macro is working)
Go to cell A20.
Put in the value “1”
Check the result cell B20.
If “result value” > 20, then run macro “Test” (this macro is working)
End of macro.

View Replies!   View Related
Color Entire Row When Cell Condition Met
Using conditional formatting is it possible shade an entire row when a cell is = to 0. I am currently creating a spreadsheet for a small amount of inventory. I am manually editing the amount of product available.

What I want to happen is when I replace them number with the text ‘Sold Out’ I want then entire row to automatically grey that row out. Is it possible to make this happen using conditional formatting?

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:


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
Copyright © 2005-08, All rights reserved