Sum Based On Multiple Conditions
I am trying to sum numbers based on three counditions.
My problem the third condition needs to use a wild card. I have attached a sample file, in the last condition I am only wanting to sum numbers where the characters in the last column begins with bd*
The yellow cell contains the formula I am using.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Sum Based On Multiple Conditions & Variable Range
I have a rolling calendar worksheet that I need to create a formula to auto populate the sum of 8 weeks out, 4 and 8 weeks back based on any work week date I may plug in. So on the "summary page" based on a work week date, I need 8 weeks out, 4 and 8 weeks back projections to be auto summed for 10 different part numbers. The "13 week rolling" page will continue to be updated so that everyweek another week is added and the last week will fall off. (this part can be done manually if no function can delete the oldest column) When the weeks are added it must include the workweek in question. i.e. ww25 = (8weeks out) ww25:ww32 / (4weeks back) ww25:ww22 / (8weeks back) ww25:ww18. see example workbook attached. This has been driving me nuts for a few weeks now.
View Replies!
View Related
Sum With Multiple Conditions
see attached workbook. I need to create a conditional sum in the report tab in the yellow cells that will sum all the values from the list sheet, based on the following conditions. 1) The name is in the list 2) The code in the list is one from the codes tab. I saw somewhere that I need to put an 'OR' statement in the conditional sum but I can't get it work. It seems to add up values for the code 'HOLIDAY' and I don't want it to.
View Replies!
View Related
Sum On Multiple Conditions In Multiple Columns
My data looks somewhat like this : X Y Z DOU Quarter Sum 0 Q1 = sum of 1's in Q1 1 Q1 = sum of 1's in Q2 1 Q2 = sum of 1's in Q3 0 Q2 = sum of 1's in Q4 1 Q3 1 Q1 1 Q4 0 Q2 A cell in the SUM column must contain the sum of 1's in column DOU if under column Quarter the value is Q1. Similarly for Q2, Q3 & Q4 if only the corresponding value under DOU is 1. Its like.. count the number of 1's per quarter and come up with the sum in 4 distinct cells for each quarter.. i.e. quarter-wise sum/count of 1s
View Replies!
View Related
Sum Based On 2 Conditions
I want to have SUMIF and IF functions to be combined. i tried using the formula SUMIF( Timesheet!D2:D55,B2,Timesheet!F2:F55) but i want to edit the range everytime. Can i have formula like if name = Ant merce and prj = Implementation support, then the sum will be 15 for the sumrange of Timesheet!F:F (entire F column)
View Replies!
View Related
Sum Ranges With Multiple Conditions
# of Project Names that are under Status=Contract (Amount in $’s ) & # of Trks # of Project Names that are under Status=V Verbal (Amount in $’s) & # of Trks # of Project Names that are under Status=No ($ Amount lost) & # of Trks lost # of Project Name that are Lost to (Grandview) that are under Status=No ($ Amount lost) & # of Trks lost Must be able to add more lines & have instructions so I can do it myself for the future.
View Replies!
View Related
Determining A Sum Total When There Are Multiple Conditions
below the following lists (Name, Week, Amount) I am trying to calculate a sum of the Amounts column (Column D) for two conditions: a particular week # (Column C), for a particular color (Column A). Is there a simple formula to SUM numbers when it is based upon two conditions (color and week #)? For example, there are two separate RED amounts that should be received in Week 4 - how do I write a formula for the cell that correlates RED and WEEK 4 that states "sum the amounts for the Name: Red and Week: Week 4"? NameWeekAmount GreenWeek 1 5,000 RedWeek 4 13,000 BlueWeek 1 1,500 GreenWeek 3 3,500 GreenWeek 2 4,200 PurpleWeek 5 1,200 YellowWeek 4 500 GreenWeek 3 25,000 RedWeek 4 13,000 RedWeek 3 12,500 RedWeek 1 10,000 BlueWeek 5 9,000 BlueWeek 3 14,500 YellowWeek 5 3,300 GreenWeek 2 1,000 YellowWeek 4 30,000 Week 1Week 2Week 3Week 4Week 5 Red Yellow Green Blue Purple TOTAL
View Replies!
View Related
Trying To Use SUMIFS To Sum A Column Based On Two Conditions
I would like to use a SUMIFS statement to sum up a column of numbers based on two conditions. The first condition is a word. I have this done. The second condition is going to be a two week date range, which I am having problems solving. Here is my formula that works so far. =SUMIF(Data!C:C,"Office",Data!D:D) Now I would like to SUMIF a second condition is met. The second condition is a date and time in this format. 1/9/2010 9:21:49 AM I am only concerned about the date. Time does not matter. I cannot figure out how to specify a date range for the second condition. I am trying to specify between 1/1/2010 and 1/14/2010 as a test. So here is what I have so far with a SUMIFS. =SUMIFS(Data!D:D,Data!C:C,"Office",Data!A:A,"Cannot figure out how to specify dates") Maybe my logic is wrong...maybe I need to do three conditions? Where column A has a date < 1/14/2010 and a condition that specifies a date > 1/1/2010.
View Replies!
View Related
Sum Values Based On Two Date Conditions
I want to populate two cells (the yellow ones) with the start and end date values from the row where a match is found in the 'period' column for the value in the left-hand green cell and in the 'week' column for the value in the right-hand green cell. Is anyone else on the board firing on all/most cylinders today?
View Replies!
View Related
Sum Multiple Conditions & Subtract Result
Attached is a section of a spreadsheet containing many columns and about 20000 rows..these rows are made up of events.. named in column a. In column F I want A FORMULA to print the difference between each rating(in col C) and rating of THE ROW identified by a 1 IN the favrank col(col e) in that event. I did the first event manually to show what I mean.
View Replies!
View Related
Summing Based On Multiple Conditions
I have been trying to search an array and cannot find the correct combination of functions to get the information I want. I might need a macro to do this but I will try anything. Here is an example: 1 A 1 1 B 2 2 A 3 2 B 4 3 A 5 3 B 6 I want to search for 3 and A and return the value in the 3rd column. I have tried several functions but cant figure out how to make sure I get the correct value in the 2nd column that corresponds to the value in the 1st column.
View Replies!
View Related
Cell Value Based On Multiple Conditions
I need to create a macro that will calculate a value based on the contents of multiple cells. Looking at the example attached, if columns A and/or B (employee ID and name) are empty, then allowable OT (G) should be 0. If either have data, then if Stage (F) is CAN, G should be 20. If Stage is FAS or FAR, G should be 10. I'm not very savvy with IF statements, which is how I imagine this can be done, and I don't know if this would be easier to do as a macro or as a formula within G. Since the contents of the of the cells will be changing on a weekly basis, I'd prefer the formula to only be there if there is content on the line, so we don't have nulls showing.
View Replies!
View Related
Average Based On Multiple Conditions
Im currently trying to report on cycle times for a certain task that is carried out on a daily basis. I have used NETWORKDAYS to calc each rows cycle time in Cell R14:R512 the code is:- =IF((NETWORKDAYS(O14,N14)-1)*($AH$11-$AH$10)+IF(NETWORKDAYS(N14,N14),MEDIAN(MOD(N14,1),$AH$11,$AH$10),$AH$11)-MEDIAN(NETWORKDAYS(O14,O14)*MOD(O14,1),$AH$11,$AH$10)=0,"",(NETWORKDAYS(O14,N14)-1)*($AH$11-$AH$10)+IF(NETWORKDAYS(N14,N14),MEDIAN(MOD(N14,1),$AH$11,$AH$10),$AH$11)-MEDIAN(NETWORKDAYS(O14,O14)*MOD(O14,1),$AH$11,$AH$10)) This is returning the cycle time providing both the start date/time and end date/time are filled in, or it returns blank if nothing has been filled in. I have then tried to calc the average overall cycle time in Cell R12 by using the below formula = SUM(R14:R513) / (COUNT(R14:R513) - COUNTIF(R14:R513,0)) This seems to be working proved i have two rows of data filled in. My problem lies when all the cells are blank, it then returns the DIV error. I have tried using an if statement to ignore if the cell is blank but it still returns the DIV error. Im really at a loss on this and now im not even sure if i have gone about this in the right way or if there is a better way to approach the whole task? I have attached a sample workbook to try and better explain.
View Replies!
View Related
Flagging Duplicates Based On Multiple Conditions
Flagging Duplicates based on Multiple conditions.. I have attached the file for reference with Dummy Data as the actual data runs in thousands... What I require is to flag Duplicates with some formula so that I can filter and then later delete those specific rows from the records... The Duplications has to be considered on Multiple criterions: 1. If the FNAme and LName is appearing for one Company more than once. 2. Sometimes there would be inconsistency in Data Entry, so the FNAme would be typed in the Column for LName and vice-versa.The code needs to understand the same. 3. Alongwith the above, if the Designation is different then the record with the Lower Designation based on the Weightage should be Flagged as "Duplicate". Ex: - 4. If there's a tie between the same designation then the record with the Maximum information across all the columns having Max info should be Unique. Ex:- The amount of details mentioned in the following columns would determine the record to be termed as Duplicate or unique 5. If still the records are identical then the first occurence can be unique... I have made this data to give an example as the actual data is very confidential. Please allow room for adding New Designations as the Designation and their Weightage needs to be Dynamic. Please refere the Word doc Duplication Criterion Explanation for the explanation.
View Replies!
View Related
Counts Info Based On Multiple Conditions
I am trying to get a list which counts info based on multiple conditions but I can find no way round this one point I have a sheet which has all the games played in a particular division and the time that each goal was scored and I am trying to break down the following What I want is a list that has a column that has the result of how many times when team A were playing at home was a goal scored between the 45th and 65th minute column A = Home Team B=Away Team C= Time of First goal D=Time of Second goal E=Time of Third etc I have a countifs in place for counting the home team and counting if the first goal was scored in under 10 minutes (this is simple as obviously all the first goals are in column C) but what I want to do is be able to have a count of how many times a goal was scored between x number of minutes eg between 40-50. My problem comes when trying to count the goal times as some matches will have no goals and the cells will be blank and others will have multiple cells filled and the figure I want could be in any column and I also only want it to count only the amount of games a goal was scored between those times so if a game had 3 goals scored in between that time I only want it to count 1. The sheet has a lot more columns but this is the main sticking point.
View Replies!
View Related
Lookup Unique Based On Multiple Conditions ...
I am looking for a solution other than using an advanced data filter for unique records only. I would like to take a large list (columns A:D), and automatically filter for unique records and other conditions and paste the results in different tables (Group A & Group B). Group A only includes records with value ="A" in the checksheet column. Group B includes records with value <>"A" in the checksheet column. Does anyone have any ideas how to do this so that when I paste the large list in columns A:D, the other tables (Group A & Group B) are automatically populated? SEE ATTACHED:
View Replies!
View Related
Finding Information Based Upon Multiple Conditions
I want to populate separate tabs from data in a master table based upon multiple conditions shown in the separate tabs. Each tab is a summary of a particular engineer's shifts over multiple weeks and their results on various projects. Each summary tab has the engineer's name and the number of the week (the two conditions). Example: On Aaron's tab, I want to show a summary of Aaron's work and his results over a period of several months, as well as who is working the other shift on that day for those projects. So I need to get the following information for Aaron's tab from the master table: Week 1, Aaron is working the morning shift and Zeb is working the afternoon shift, Week 2, Bill is working the morning shift and Aaron is working the afternoon shift Week 3, Aaron is working the morning shift and John is working the afternoon shift etc etc I also need to know how much work each engineer pairing has achieved in a particular week on a particular project. The engineer working in each shift may vary from week to week, as will their results. There is no formula for when a particular engineer will be working either morning or afternoon or whom they will be working with.
View Replies!
View Related
Lookup Value From List Based On Multiple Conditions
I have a list of holiday data which has the following info. Name, Date from, date to, hours off per day. I need to look up this information and place the number of hours against each day for each person. The problem I am having is that I can't get the hours into the cells for which the date is between the date from and date to. I have attached spreadsheet and how I want the summary page to look. I just need the correct formula inserting.
View Replies!
View Related
Create New List Based On Multiple Conditions
I am trying to filter a range of text and copy only those entries from it that meet selected criteria into a new part of my page. The entries will all look like the following but will have varying numbers before the final Equity/Index part: IBM US 3 C60 Equity IBM US 3 P60 Equity UKX 3 P5000 Index UKX 3 C5000 Index I have constructed something using a modification of the find nth word function from this site that allows me to filter based on whether the fourth part (or for the third and fourth choices, third part) shows C or P at the beginning and then based on the value that is written after the letter. However, I have to break down the code a bit further to firstly check whether the final word of the string is Index or Equity to decide which word to check for C/P. I was wondering if anyone knew whether there is possibly a more simple way of running a find within one cell than using the nth word function. Would it possibly be a case of making two subs within a different module and then calling them to look for the specific word number based on what I have in F6 (my original equity/index ticker symbol: for example IBM US Equity or UKX Index)? Attaching code below. I'm sure it doesn't make a lot of difference but in case it helps I am currently running Excel 2007 on a Vista machine but I also use it at work on a Win XP PC with 2003. ----CODE----........................
View Replies!
View Related
Multiple Conditional Format Conditions Changed Based On Another Cells Value?
A1:A6 have numeric values 1 to 6. I want the conditional format values to change Based on the value in Cell B2. If the value in B2 is "old", then I want these conditional format conditions for A1:A6. Numbers between 1-2=green text, 3-4=orange text and 5-6=red text. IF the value in B2 is "new" then I want the conditional format conditions in A1:A6 to change so 1=black text, 2-3=green text, 4-5=orange text and 6=red text. I know how to do multiple conditional formats but i have no clue how to change multiple conditions based on another cells value.
View Replies!
View Related
Change Cell Background Color Based On Multiple Conditions
My Excel skills are basic at best, with no knowledge of VBA other than finding the VB Editor. What I'm trying to do: Attached is the file I'm working with, to give you a better idea. When a name is selected in column B, the cells in the non corresponding columns E to N should have a black background color. For example, when you select Mike in column B, the cells in that row in columns I to N, named Dave and Dean, should become black. If you select the name Dave, columns E to H (Mike) and M to N (Dean) should become black. In addition, the background color of the non-black cells should change depending on the status. OK : green FAIL: red N/A: orange Exceptions: blue The attached file shows a few possible results, manually entered.
View Replies!
View Related
Sum Based On Multiple Columns ..?
My worksheet has info in columns A,B,C that lists item, size and color. In column E is the transaction count. In G,H,I and J I have cells that I want to total the data into. There are many rows, depending on sales for the day. I want to sum each item daily so I need VBA code that would say "If A,B,C matches G,H,I then enter amount from E in column J". A,B,C may have multiple rows that would match G,H,I (ex = Shirt, Med, Red or Sweater, Lg, Gren) that need to total on each summary line in column J.
View Replies!
View Related
Sum Based Upon Cell Color - Multiple Times
I have a workbook with a tab called "parts list" where there are thousands of parts with prices. I need to sum all of the parts based upon their location within the list. The list is divided with the following headers - Yellow (Assemblies), Gray (Sub-Assemblies) there can be many under a yellow header, and Green (Components) there can be many under a Gray header. I have been summing the areas manually, and I am hoping for a more automated way of creating the totals by running a macro. I need to sum (Column K) from Yellow+1 to Yellow-1 and deposit the sum in the starting Yellow row - Column L. This needs to be repeated for as many Yellow Headers as there are in the list. I then need to repeat the process for Gray Headers - Col K, Gray+1 to Gray-1, and put total in starting Gray header, Column L. Once again, there can be many Gray Headers under a Yellow header, and this needs t be repeated throughout the list. I then need to repeat this for the Green Headers - same as above Gray. I am hoping that this can be done by color, but if it's easier, I can sort the list by color and in column M, add an "A" to all Yellow Headers, "S" to all Gray Headers, "C" to all Green Headers, and "E" to represent the end of the component list -
View Replies!
View Related
Color Cells Based On Multiple Conditions Of Different Cells
I’m trying to change color in cell B9 according to different conditions in Cell C9 (than on b10 according to c10 and so long until necessary - probably will be around 2000 lines). Since there are more than 3 conditions and in two cases condition depends on the color of the C Column, I can not use conditional formatting. I’ve searched and found similar forums here, but since I’m ignorant in VBA code, I couldn’t manage to make adjustments. So if you can help me with the code, I would really appreciate it! Condition and Results required would be: IF column C = “S” than on Column B = color cell light blue with white border IF column C = “P” than on Column B = color cell Green with white border IF column C = “A” than on Column B = color cell Yellow with white border IF column C = “L” than on Column B = color cell Red with white border IF column C = “C” than on Column B = color cell Dark Blue with white border IF column C = “ ” than on Column B = color cell white IF column C = “V” and has white background than on Column B = color cell white IF column C = “V” and has a Dark Grey background than on Column B = color cell Dark Grey I don’t know if the last two are feasible.
View Replies!
View Related
Multi-Criteria 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 Logs With Multiple Conditions Of Multiple Sheets
I've got no clue about all this, but I've had to get specific formula examples and fill in the blanks in order for my timesheet to work. There's just one final problem if somebody could please help. This is a timesheet for a 5 day work week. I need to count the number of unique log numbers for a specific activity. The log numbers counted must be unique across the entire week, not just for each day, which means I want the formula to count the unique log numbers across multiple sheets. The formula also has multiple conditions. I got 2 columns. The first part of the formula needs to verify a word, say, "split" and if it does it checks the adjacent cell for a unique log number. If both arguments are true, it counts the log as 1 unit. Here is a working formula for only one page. =COUNT(IF(D4:D29="split",IF(FREQUENCY(C4:C28,C4:C28)>0,1,))) Here's 2 problems with this formula: 1. I will count if it encounters a blank cell in the Log numbers the first time (which will happen as not every activity we do has a log#), but it will stop counting if it encounters a second blank cell. 2. I don't know how to make it work across several sheets. This is an alternate formula which works and skips the blank cells, but I don't know how to add the multiple condition of "split" and to have it work across multiple sheets. I just copied it Microsoft. As I said, I don't understand it, I just fill in the blanks. SUM(IF(FREQUENCY(IF(LEN(C4:C29)>0,MATCH(C4:C29,C4:C29,0),""), IF(LEN(C4:C29)>0,MATCH(C4:C29,C4:C29,0),""))>0,1))
View Replies!
View Related
Sum With Two Conditions
I want to calculate the sum of column with two conditions on different worksheets, Example Data Stock #QTYMonth 01335208Aug-06 01337402Aug-06 01337392Aug-06 01335400Sep-06 01337100Sep-06 01337102Sep-06 If the stock # is equal to the stock # and Month equal to month I need to get the result in another sheet as below. Stock #Aug-06Sep-06 01335208400 01337794202
View Replies!
View Related
Sum Under Two Conditions
what would be the most effective and easiest formula to use if I want to sum a range of numbers meeting 2 conditions (or more).. eg.. sum all the unpaid invoices (condition 1) due on March (condition 2).
View Replies!
View Related
Sum Between Conditions
I'm (and you are too!) still helping to streamline a charity's spreadsheet. There are two worksheets that this question refers to: "DD Tally" and "List of DD Donors" (where DD stands for Direct Debit). In the DD Tally worksheet we are trying to summarise the information contained within the List of DD Donors. We would like to be able to estimate how much, in direct debit donation, we will recieve in the financial year (currently, April 06 to March 07). We would like this to be seperated into our monthly, quarterly, semi-annual and annual donors. The challenge is knowing how to search through a column to find the, for example, "Monthly" entries and once these have been found to find the quantity of the donation(s) in the rows with "Monthly" in them, and then, to determine the number of months they have been paying for during the financial year (their first payment dates are listed as 01/MM/YY) - i.e, the difference between the end of the financial year (03/07) and the start (04/06) or between the time they joined during the financial year and the end. Then to multiply the number of months the direct debit has been active during the year by the amount they donate.
View Replies!
View Related
Sum Unique With Conditions
I am looking for a function or group of functions that will: Match Column C and for every unique item in column A, it will sum column D. Example: Column A: Column B: Column C: Column D: 12346 Level1 ACME 1 12346 Level1 ACME 1 12346 Level2 ACME 1 12376 Level2 ACME 2 13255 Level2 ACME 1 13548 Level1 ACME 4 12356 Level1 WIDGET 5 12356 Level2 WIDGET 5 12376 Level2 WIDGET 6 12378 Level3 WIDGET 2 12378 Level1 WIDGET 2
View Replies!
View Related
Conditions To Sum Every Other Row
Although I am not getting any errors, I am not having success returning proper answers with the formula below. Perhaps I cannot do what I am attempting. =SUMPRODUCT(IF(AND($K$22:$K$169="A&B",YEAR($G$22:$G$169)-YEAR('Info'!$F$9)=0),(MOD(ROW($R$22:$R$169),2)=0)*($R$22:$R$169),0)) where: K22:K169 are user entered categories from a drop down list G22:G169 are user entered dates F9 is a user entered update date column R are monetary values with first row being a revenue$ second row margin$ I need to summarize every other row (one row is revenue, one is margin) if the two conditions are met.
View Replies!
View Related
Sum If Two Conditions Match
I need a formula that returns the sum of cell N12 from multiple worksheets where cell A9 matches a particular month and cell K7 matches a dept name (such as Scientific). To explain further, in say 50 worksheets, I want to pick out all the March sheets, for Scientific, and sum all the N12 cells from those sheets. I've been trying to alter an array formula i've used previously, but gotten completely lost.
View Replies!
View Related
Sum Formula With Conditions
I'm having toruble developing a formula to allow me to add all the values in cells A1 through A100 if their respective quantities are more than 0. What i mean is if i have the following columns of data: A B 6 2 2 1 4 0 A being the values added and B being the quantity then i only want A1 and A2 Added together. Therefore the value would be 8.
View Replies!
View Related
Reporting With SUM And IF Three Conditions
I have been trying to create a report that involves three conditions, but so far I have had no luck using SUM and IF conditions to do this. I have attached a file with an example of what I would need. Basically, I would need the "Resolved" and "In-Progress" quantities filled in below the "Country Report" for each respective country.
View Replies!
View Related
Finding The SUM Using Various Conditions
I'm not sure if this is the best way to accomplish this task, but I've been trying to use countif to calculate the total of column C in my data based on the conditions that its respective cell in column A must be found and that column B must be yes. I can't use countif as it doesn't allow criteria relative to cells. Is it possible to use SUMIF?
View Replies!
View Related
Sum: Many Conditions-Criteria
I have recently started using formulas in Excel therefore cannot really crack this problem down. In my worksheet, I have a column (A2:A200) which stores dates starting from Jaunuary 1st 2006 to January 1st 2007. The second column (B2:B200) stores the expense type (i.e: Maintanence, gas, lease payment), and the third column (C3:C200) stores the amount spent on the given date and expense type. What I would like to do is to create a formula which calculates the amount spent on maintanence, gas, lease payment during January, February, etc:
View Replies!
View Related
How To Satisfy Multiple Conditions Using Multiple Criteria
I'm trying to divide my data into 6 different groups, based on 2 different criteria. First, I am not sure how to write the logical test to take 2 columns of data into consideration (using "&" and "AND" do not work; I am not sure what else to try), and second I can't figure out how to write the formula so that it can select from 1 of 6 conditions. So, overall here is what I want: If DL2=3 and CK2=1, then I want this to be labeled as '1' If DL2=3 and CK2=2, 2 If DL2=1 and CK2=2, 3 If DL2=2 and CK2=2, 4 If DL2=1 and CK2=1, 5 If DL2=2 and CK2=1, 6 These 6 conditions cover all possible combinations of numbers in the two columns.
View Replies!
View Related
Remove Multiple Data By Multiple Conditions?
1. I have 4 columns (let's say D,E,I,J); D E I J Account1 Sum1 Account2 Sum2 151 93,79 BF2479 30,00 1476757 93,79 BF7 22,00 BF2479 30,00 151 93,79 86041 84,03 23235 30,00 534 6608,45 2. I need to clean the multiple identical data in the list by both Account no. and Sum (of course the list is much longer!) so that the list should look like this: D E I J Account1 Sum1 Account2 Sum2 1476757 93,79 BF7 22,00 86041 84,03 23235 30,00 534 6608,45 I must say that I am a beginner in Excel, so I tried to use this: =IF(OR(E5=$J$5:$J$430);IF(INDEX($I$5:$I$430;MATCH(E5;$J$5:$J$430;FALSE);1)=D5;"";E5);E5) for column E, and =IF(OR(J5=$E$5:$E$452);IF(INDEX($D$5:$D$452;MATCH(J5;$E$5:$E$452;FALSE);1)=I5;"";J5);J5) (which is reverse of the first one) for column J; (The ranges are the ones used in the real sheet), not for the ones in the example above!); The formula does seem to eliminate a dew unwanted data, but does not give me all the wanted results!
View Replies!
View Related
Macro Sum Activesheet With Conditions
Group the same cells in each row, if found, in Column 2 that refer to same cells in Column 1 and sum the cells in col 3 and 4 that refer in column 2. Col 1 Col 2 Col 3 Col 4 1 272 0125 200 10 2 272 0125 100 5 3 273 203 500 85 4 274 ................
View Replies!
View Related
Sum If With 2 Conditions And A Wild Card
My live sheet is a lot more complex but this will help me well on my way. Ill let the example say everything: Sheet3 ABC1Condition 1Condition 2Condition 32red lorrybig £ 92,621.98 3yellow carbig£ 145,498.33 4yellow vanbig£ - 5pink carsmall£ - 6red carsmall£2,552,404.50 7pink lorrybig£ 862.37 8green carbig£ - 9green lorrybig£ 707,083.31 10red vansmall£ 30,010.73 11green carbig£ 2,276.13 12 13 14Total of all cars that are big: 15expected answer £ 147,774.46 Excel tables to the web >> Excel Jeanie HTML 4
View Replies!
View Related
Sum To Unknown Rows With Conditions
Need to sum through an unknown number of row generated by advanced filter at run-time Formula at C16 Data starts at row 57 (from copy/paste - advance filter) Last row unknown Criteria to match in A16 Data to be evaluated in B57:B (row unknown) Found this formula on microsoft.public.excel.misc =SUM(A57:INDEX(A:A,MATCH(9.99999999999999E+307,A:A))) Which I modified formy start row
View Replies!
View Related
Check Sum Then More IF Function Conditions
=IF(H10=0,IF((R10-I10)>$AH$1,(R10-I10),0),IF((R10-N10)>$AH$1,(R10-N10),0)) I have a small issue with the formula above. Basically the sheet is used to input start and end time of shifts and create breaks and lunches accordingly. The formula above resides in column S. Basically it warns us if the end of the shift is over 4.00 hrs past the end of the last lunches. A shift can have no lunch, one lunch or two lunches. H10 would be the start of the first lunch, R10 is the end of shift, I10 is the end of the first lunch, AH1 = 4:00 or 4:00:00 AM, N10 is the end of the second lunch. What happens is that if there is no lunch given in cells H, I, M, N or in this case H, I, N then I get a strange number. For instance, I put in 6:00 for the start time [E10] (actually we input military time and the result is displayed in AM or PM) and the end time as 8:00, then I get a result of 8:00 in the S column [titled +4hrs warning). 6:00 to 9:00 results in 9.00 hrs., etc, When we input a six hour shift, a lunch is automatically starts in H10 and ends in I10. Once this begins, the formula works correctly.
View Replies!
View Related
Sum The Range Of Data If Two Conditions Are Satisfied
My data is as follows MonthMilesAmountLocation June 8£3.20 Loc1 June 8£3.20 Loc1 June 8£3.20 Loc2 July 8£3.20 Loc2 July 8£3.20 Loc1 July 38£15.20Loc1 July 4£1.60 Loc2 Aug 38£15.20Loc2 Aug 4£1.60 Loc1 Aug 8£3.20 Loc1 Here is what I want to do - Avoide filtering and manually calculating data for the total expense in the month I want to SUM all the (3)AMOUNT in the (1)MONTH of JUNE for (4)LOCATION LOC1 I know its too much to ask for but can you suggest if i can combine SUMIF or SUM(IF) or any other formulas ???
View Replies!
View Related
Count And Sum Cells Meeting Two Conditions
I'm working out a schedule for work. Row 1 contains 31 days(columns), Row 2 28 days, Row 3 31 days...and so on for the 12 months of the year. I've formatted each Friday, Saturday, Sunday and Holiday with color. Fridays are blue, Saturdays are green, Sundays are yellow, and Holidays are red. Monday-Thursday are no color. Next, I fill in each day with an employee name. Now the hard part...I want to count the number of times an employee name falls on a Monday-Thursday, Friday, Saturday, Sunday and Holiday. At the bottom of the worksheet I'd like to see something like this: Jones: Friday 4 (total number of days jones is in a blue box) Saturday 5 (...on a green box...and so on...) Sunday 3 Holiday 2 Monday-Thursday 50 For each employee name. Sounds easy, right? I can't get it to work!
View Replies!
View Related
If ValueA Appears In Different Sheet, Sum ValueB With Conditions
I’m having difficulty writing a formula to solve the following dilemma: I have “Sheet1” with staff details and each staff member may be listed more than once. I need to use columns C (StaffID) and L (Load). Load values may only be 1.0 or 0.5. Then I have “Sheet2” which is being used to compile data from a range of sources – this is where I need to put my formula to get my answer. “Sheet2” has column A (StaffID) and each ID will only appear once on Sheet2. The formula required needs to solve “if the StaffID from Sheet2 appears in Sheet1 (keeping in mind it may appear multiple times), sum the Load values for that StaffID provided the Load value on each line only equals 0.5". If the StaffID doesn't match, the cell return is to be 0. I will then need to do this again in another cell where the Load value only equals 1.0 (obviously just amending the condition value in the formula).
View Replies!
View Related
Sum By Conditions & Rolling Monthly Total
I have a pivot table that summarizes expenses (cash advances, cashe remitted, etc.). The issue that I'm having is the way the data is displayed on my pivot table. When I adjust the custom calculations to "show data as Running Total in MONTH" I get the desired outcome on my row totals, but I do not get the correct figures on the actual data within the Pivot Table. When I remove this custom calculation and just "sum by value" then the data is correct, but the row totals are not. In a perfect world I would need the values to sum by value, while the row totals are set to "show data as running total in MONTH". I'm not smart enough to figure out how to produce both.
View Replies!
View Related
IF Formula For Multiple Conditions
I need to set up something like an IF formula that will populate 1 cell based on which of 11 possible values will be represented in another cell. I can do this up to 7 with no problems using the following IF statement: = IF(G23="1","a", IF(G23="2","b", IF(G23="3","c", IF(G23="4","d", IF(G23="5","e", IF(G23="6","f", IF(G23="6","g",IF(G23="7","h", "OTHER")))))))) But if I try to put more than 7 conditions in there it tells me I've entered too many arguments. Is there a way I can either use more than 7 arguments or some other formula
View Replies!
View Related
|
TRACKER
