Sum A Column When The Quantities Meet Two Criteria
I need to sum a column when the quantities meet two criteria. The name in one column, and the date in another column. Probably an easy formula, but new at this and having trouble.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Sum Rows In Column That Meet A Criteria
I have a filtered list of data expanded to "all" in each column. One column is "percentile" (25%, 50%, 75%, etc). Search down the "percentile" column until you encounter 25% as an entry, then sum a number in the same row from a different column with each 25% entry you find.
View Replies!
View Related
Count Where Text Value In 1 Column & Month Of Dates Meet Condition/Criteria
need a formula to calculate the total number of x's in one column (column B, C, E are training types= x) where the corresponding column date falls within a date range. It’s to total each type of training done for each month. I have 3 training type columns and a cell that calculates the total number of trainings for the each month:=COUNTIF(F2:F100,">="&DATE(2008,2,1))COUNTIF(F3:F200,">="&DATE(2008,2,31)). So now I just need it broken down by training type per month. How many x's in each column for February as an example.
View Replies!
View Related
Sum Values That Meet Certain Criteria
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 Replies!
View Related
Look For Cells That Meet 2 Criteria
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 Replies!
View Related
Colour Any Cells That Meet The Criteria
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 Replies!
View Related
Short List When Meet A Certain Criteria.
I need to extract key cell info from a master list when one of the cells meets a certain criteria. Example: Each row of the main sheet represents a Purchase Order with columns AM each containing text, numeric, or currency values regarding that PO. Column F represents a 4digit activity code that defines exactly what was purchased. If the activity code for a row is in the 1000 series (1000 1099) I need to assign info from 3 other columns (say B, E, &G) to a subsequent sheet for tracking. My limited Excelovision has built this: (sheet 1 represented as FY10) =IF(AND('FY10!F4>=1000,'FY10'!F4
View Replies!
View Related
Counting Even If Doesn't Meet Criteria
I am trying to use this function to figure out averages for tests. there can be up to 25 tests in the sheet which is where the "YES" comes in. If the test booklet was used for this class then YES will be placed in the cell in field B2:B26. Now thes issue I'm having is that the formula is doing the math for the fields C2:C26 if there is a number in them if there is even if it does not say YES in the B2:B26 field. Here is the formula/function I'm using: =(COUNTA(C2:C26))/COUNTIF($B$2:$B$26,"YES")
View Replies!
View Related
Listing Cells That Meet A Criteria
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 196) that has an Acid Number > 0.1 . Is there a formula to do this?
View Replies!
View Related
SumIf Two Columns Meet Criteria
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 Replies!
View Related
Sum Range Where Corresponding Cells Meet Criteria
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 Replies!
View Related
Summary Count That Meet Criteria
In my spreadsheet I have several columns and I have written a formula that has two conditions. If these are met the result is counted. However I want to add another condition. I need to write a formula that displays information relating to the conditions in a cell if the other two conditions are met. For Example, if the formula picks out that a row has the word 'John' and has the a number between 1 and 14 it will copy the inputted data at the beginning of the row.
View Replies!
View Related
Delete Rows When Meet Criteria Or Condition
I have a spreadsheet with 3023 rows and columns AL. Here's a small sampling of Column A (with heading "sku" included, copied exactly as it appears in the spreadsheet): A sku 31020400100000 31020400200000 31020500100000 31020500200000 31020600100000 31020600200000 31020700100000 31020700200000 I need to delete every row in which column A includes the text "200000". Although the above sampling shows "200000" appears in every other row, that is not the case in all 3023 rows.
View Replies!
View Related
Delete Visible Lines That Meet Criteria
I'm trying to apply an autofilter to do the ol' "delete visible lines that meet criteria" trick. Unfortunately my vb macro, although applying the string correctly, hides all of the lines... This is the part of code relevant to filtering (i'm trying to filter on column M which is 13):
View Replies!
View Related
Counting Unique Entries That Meet Certain Criteria
Every other line is the planned production and the others are filled in after as the actual production I have a sumproduct formula right now that looks if the line is considered planned production and then if there is a number greater than 0 planned on that day. The idea is to tell me the number of Stations scheduled to run that day. Here is my problem if a station is running two different products it gets counted twice. I am trying to find a way that once a station is counted it only gets counted once even though they are on seperate lines. Attached is an example of what I have with the formula and below that is what I am looking for
View Replies!
View Related
Calculating On Values On Calender That Meet A Given Criteria
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 a1b1 to get the number of days, multiplied by costs. This particular spreadsheet is laid out in calender format, hence the problem)
View Replies!
View Related
How Do I Count Nonblank Cells That Meet Criteria In Another Cell?
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 Replies!
View Related
IF Formula  Skip Rows That Dont Meet Criteria
I have a spreadsheet which contains data for crime incidents occurring in a month. I have created a worksheet which uses "IF" statements to pull in data that occurred within the last 24 hours only. I would like to create a report that can be printed which has a crime category and lists all the incidents for that category below that occurred during the day. The issue I am having is that all "IF" and "LOOKUP" statements are line for line and I want to be able to skip blanks or where it is not the category in the title.
View Replies!
View Related
Delete Rows Where Multiple Columns Meet Criteria
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 Replies!
View Related
Delete Rows Where 2 Columns Meet Multiple Criteria
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 Replies!
View Related
REcord That Fall Within Date Range, AND Meet Text Criteria
I have a large database that is updated daily. From within the database I need to: 1. Select all records where date field A is 14 days or less than date field B AND 2. Where a field C matches a text criteria, i.e., =DOGGIE AND 3. Append KITTY to field D of all records that match criteria 1 and 2 Finally 4. SAVE results as a text file.
View Replies!
View Related
Copy Data From Another Sheet If Adjacent Cell Meet Criteria
I have a have excel problem Im trying to figure out. I have 2 sheets. Sheet1 and Sheet2. I want to copy into sheet1 cell B2 the contents of sheet2 column C row x iff sheet2 column B row x = 1. There will be only one cell in that column equal to 1, so I will be copying that cells(colB; rowx) adjacent cell (colC;rowx) contents. This is what I have so far but it doesnt work. =IF('Sheet2'!B:B=1,DONTKNOW WHAT TO PUT,""))
View Replies!
View Related
Count Unique Entries In One Column That Meet Conditions
I tried to ask this question yesterday  but it was a followup question stuck at the bottom of a thread. So, with your indulgence, here is a simpler version of the question, complete with an attached spreadsheet, if you wish to use it. I also closed the other thread by marking it "Solved", since it answered my initial question.] The situation: I have two columns of data. The data is not in alphabetical order, and every column includes duplicate values. namegender jones m martinf smithf collinsf wilsonm jones m martinf hughesm wilsonm martinm smithf west f jones m west f martinm The challenge: In one cell, count the number of unique names that appear in the name column 3 or more times... with the additional condition that each unique name (which appears at least 3 times) must include at least one one woman! The correct result: ...
View Replies!
View Related
Calculating Daily Quantities
I have 3 worksheets: Income; Expense; Consolidate. In the first two sheets i am entering, by dates, quantities that are getting in and out of the warehouse. My code copies that information in the consolidated sheet. What I need is to make a code that Calculates the "Daily Quantities" and "Rent", based on quantity in the warehouse, that I am paying each day.
View Replies!
View Related
Formula For Material Quantities
I am trying to get a formula for material quantities What I want to do is If length is less than 3mts I require 2 If length is more than 3 meters than I require 3 and 1 more for every 3mts after that e.g: 2mts = 2 3mts = 2 4mts = 3 6mts = 3 7mts = 4 9.1mts = 5 and so on
View Replies!
View Related
Match Quantities Of Buy And Sell
I want to match the quantities of buy and sell appearing the same line but in different columns based on FIFO. In case of mismatch, the excess quantity should be transferred to a new line below the same column in which there is a mismatch.
View Replies!
View Related
Multiply Quantities Based On Lookup
I have a Bill of Materials (BOM) that has been flattened from a database into an Excel format. There are many BOM levels and I need to multiply the quantity of a component by the quantity of the "parent" it reports to. I have attached a sample file with raw data and the expected result (calculated manually). My goal is to create a formula that calculates the "Ext. Qty" column in my expected result. EXT_QTY.xls Here are some of the rules for the raw data structure: 1. First BOM level at very beginning is always "1" 2. All children of BOM level "1" have a BOM level of "2" 3. All children of BOM level "2" have a BOM level of "3"... 4. There are potentially many BOM levels (approx. 15 levels for full set of data) 5. All of the BOM level "2" items are children of the most recent BOM level "1" assembly and there will be multiple level "1" items, level "2" items, etc. 6. Data is always sequential so the children (and grandchildren) will always come after the parent
View Replies!
View Related
Distribute Quantities From Known Average Price
I'm having some problems to find a way to distribute quantities from a known average price. Here's the Example: My Client bought 100.000 Shares from a Stock, in various prices. Qtty Price 10041,71 30041,61 70041,78 1.50041,72 4.40041,68 5.50041,75 10.00041,94 13.50041,70 64.00041,80 Then the client wants to distribute this trades for 7 different funds at the same average price. Qtty Fund 1: 294 Qtty Fund 2: 541 Qtty Fund 3: 1.213 Qtty Fund 4: 13.370 Qtty Fund 5: 16.582 Qtty Fund 6: 28.002 Qtty Fund 7: 39.998 I'm sending an example attached.
View Replies!
View Related
Calculate Quantities Based On Set Total
I have three values. The Quantity of items, the cost per items and the cost for the entire order. In some instances I want to give my clients items for free so I need to lower the cost per item in order to keep the entire order cost the same. In order to ensure we are on the same page let's use the following associations: Y = Quantity X = Cost Per Item Z = Order Total So essentially I need a formula that will automatically calculate the cost per item (X) when more Quantity (Y) is added in order to keep the order total (Z) the same.
View Replies!
View Related
Individually List Grouped Items / Quantities
I have a list of items and quantities I need of each, such as cat 3 dog 2 mouse 1 horse 4 snake 2 leopard 4 I'm thinking that it would be a macro that would be way to go for this project. I want to run a macro that will make the list above the following cat 1 cat 1 cat 1 dog 1 dog 1 mouse 1 horse 1 horse 1 horse 1 horse 1 snake 1 snake 1 leopard 1 leopard 1 leopard 1 leopard 1 So what happened was that 6 lines of information was transformed into 16 lines of information. My purpose is that I will then will exploring various combination of these items, and thus I think that splitting them up like this will make them more manageable to work with. Since the original list values will change I will not always know how many lines to set aside for the individual breakdown.
View Replies!
View Related
Calculating Totals For Items With Associated Quantities Over Multiple Rows
I am trying to accomplish is to associate each product on the Distribution tab in 'E' to its associated quantity in 'F' so I can count the total number of each product and display this in 'D' on the Totals tab. Once this is done, the total for each product would me multiplied by the associated price in 'C' on the Totals tab to achieve the total item value for each item in 'E'. I have included the current results and what the correct values should be. As you can see, I tried to use 'Countif' with no success. I could not find any solutions in the forum that were close enough to this situation to work.
View Replies!
View Related
Sum Range Based On 1 Criteria Of Column & 2 Criteria Of Another
i m trying to use the sumproduct formula, and OR but i cannot seem to get this right! =Sumproduct((A1:A10="Yes"),(OR(B1:B10="Yes",B1:B10="Mayby")),C1:C10) I have also tried Array Formula as follows; {=SUM(IF(A1:A10="Yes",IF(OR(B1:B10="Yes",B1:B10="Mayby"),C1:C10)))} I have also used UDF to for the sumproduct, but cannot make that work! keep giving me value message Function Function Customer(Service as Range, Outcome as String, Service2 as Range, Outcome2 as String) Customer = Sumproduct((Service = Outcome),(Service2 = Outcome2), Result) Didnt get thru this bit to start building on the Function! keep giving me #Value!
View Replies!
View Related
Copy Cells In One Column Based On Criteria In Another Column
I have an personnel file with employee info, and I want to create a macro that will look in the "Master" worksheet at the Department column ("I") for anyone in Benefits, and then copy their name from the Name column ("D") into the "Benefits" worksheet. The names should begin pasting in cell "D3" but will recognize if a cell already has a name in it and then paste in the cell below that. Here is what I have: Option Explicit Public Sub RatingbyDept() Dim Dept As Range With Sheets("Master") For Each Dept In .Range("I2:I1000") With Dept I keep getting error 92  "For loop not initialized".
View Replies!
View Related
COUNTing Values In Column Two On Criteria In Column One
I have a workbook where I am trying to COUNT the values greater than zero in column "J" based on a selective criteria of column "B". In other words, if the date in a column "B" cell (B3:B78) is greater than the date in A86, I want to count the number of cells whose corresponding value in the "J" column is greater than "0". And then I want to take this selective COUNT and divide it by a COUNT of all the cells in the range "J3:J78". So, what I am trying to do is: FORMULA /COUNT(J3:J78)
View Replies!
View Related
Highlighting If A Range Of Cells Meet A Value
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 Replies!
View Related
Counting Cells If They Meet Multiple Criterias
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: AB12345, BP12345), 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 Replies!
View Related
Macro: If Two Columns Meet Condtion Then Highlight Row
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 Replies!
View Related
