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
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
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
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 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
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
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
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 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
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
Sum With Criteria In Another Column
I have a sheet of 2 coulmns , the first containing numbers) , the second containing (objects) , these numbers are as references for the objects , however , every object may have different referencies , i.e( car = 1 in a row , in another row car = 2) I tried to use VLOOKUP combined with OR , in order to return the car , if I entered any of references relating to it.
View Replies!
View Related
Sum Column Meeting Criteria`
how a SUMPRODUCT function can resolve this. In this sample I want to have 112032188 & 112032188W be treated the same for the Total sum qty. In this case =10. This will compute but how do I include items in column A that have 'W' suffixes for computing Total Qty? Formula: =IF(A2=A1,"",SUMPRODUCT(($A$2:$A$100=A2)*($C$2:$C$100)))
View Replies!
View Related
SUM X Column Range Based On Criteria In Cell
Im trying to do a sumif reading the name in column N (either "Business" or "Procurement"), and suming all values in the range AW:AY. this is what I have come up with.... sumif(N10:N49, "Business", AW10:AY49) but it returns incorrect values and not the whole range???? Any ideas? Auto Merged Post Until 24 Hrs Passes;here is an example of what im trying to do... H2 and H3 are my attempted formulas
View Replies!
View Related
MultiCriteria Sum Based On Multiple Column Cells
I have a large worksheet that lists employees and the hours they work each day in a pay period. For Example: Column A holds a date and Column E holds the number of hours worked for a paytype. So i want to create some function that sums the numbers in column E, but only those where column A holds a date. Then i want to return that number to a cell, say C20. I think I've managed to tell the macro which rows i want to return values from, but I can't figure out how to say "add these numbers together and put the total in C20." I'll check this frequently if clarification is needed.
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
Find, Meet Condition, Sum Range, Deduce, Find Next
i have spent 40 hours, and still didn't find a solution. Please help is need it!! example: i have to find all articles with same code (222). first one has Q =100 second one Q=250. soled Q=150 (i am talking about 5000 rows, 400 different or same articles per month 12 months) columnAcolumnBcolumnC 222 1000 11150 33370 222200200 333 and so on first i have to deduct from the first one it finds (max. till 0 ...it can not be negative) ....after finding another one it deducts the restit means 50 Is there any kind of formula with this possibility. if it is poorly writen please let me know for more info. i am not an expert in excell, but i have tried variations of sumif, vlookup functions, but i always get stuck deducting the whole Quantity from all of the same (222) articles .
View Replies!
View Related
Sum Range Using 1 Column Of Named Range As Criteria
how to use SUM Formula a column from within a Named Ranges or Dynamic Named Range? For example, if the range name "MyData" refers to the address: A1:G10, how could I sum all the numbers in column G of that range where column A meets certain criteria. Eg., Column A holds fruit names: Apple Orange Banana Apple and column G holds quantities of the particular fruit. I'd like to sum column G (quantity) for only those quantities that match "Apple" in column A.
View Replies!
View Related
Dificult Sum + Sum.if Date Range Criteria
0 x 2 x 1 x 3 x 4 x 1 max 5 x 8 x 9 x 1 x I need to sum the 1st column from the bottom, to the same line of the max on the right column. First thing i remembered =sum(xx:max(xx:xx)) but it doesn't work, i think because sum() does't allow any operations in its arguments. Second was to store the line of the max and try to join the column and then sum them but =lin(max(xx:xx)) doesn't work either Third i tried =sum.if() but i don't have much experience with this function. Also i'm trying to perfect a SUM.IF with a DATE RANGE CRITERIA instead of: =SUM.IF(I4:I25,">=2009/1/1",R4:R25)SUM.IF(I4:I25,">=2010/1/1",R4:R25) why can't i use? =sum.if(xx:xx,year(xx:xx)=2009,yx:yx) or =sum.if(xx:xx,year(xx:xx)="2009",yx:yx)
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
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
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
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
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
Lookup Multiple Criteria & Sum Result On Multiple Criteria
I am trying to solve a problem. I am currently using this formula = SUMPRODUCT((Sheet2!B2:Sheet2!B300="MARKETING"),(Sheet2!D2:Sheet2!D300="200612"),Sheet2!E2:Sheet2!E300) This formula works for me as it is but I would like to add more months to 200612. I want this to also be 200701 and 200702. In another cell there will be up to 10 months. Is there a way to do a Vlookup or something that will look up these months in another table, rather than keep typing them out in the formula?? Otherwise my formula will be very long. So the info looks like this in excel MARKETING 200612 10 MARKETING 200701 25 MARKETING 200708 50 ECONOMICS 200709 30 The info goes on and on. The two variables are the MARKETING column and the month column. My problem is that I would like a seperate table that can be the months. So 200612 and 200701 is one table, and 200708 and 200709 is another table. The table changes often so I dont want to mess with the formulas, rather a table.
View Replies!
View Related
