Count / Sum Numbers In Column Where Specific Text Exists In Adjacent Cell
One has numbers the other has "YES" or is blank. I want a formula to look at the second column and where there is a "Yes" then count the number in the same row in the first column.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Show Sum Of Numbers In One Column IF Another Column Has A Specific Category
I have a worksheet which basically tracks time. the time is reported in Column C. In that row in Column E, there is a validation list with about 6 different categories in it. On the side of this "table" I have a list of all the categories and I want a value to be next to it that reports the sum of time (C) for each category (E). So for the "Routing" category, I would want the value to be the sum of just data on the timesheet that have "routing" in Column E.
View Replies!
View Related
Count/sum Text In Column
Here I come with yet another question in excel, how do I count/sum text in a column. E.g Column B Fish Eggs Meat Fish Fish Chicken this would basically be # Fish 3, # Eggs 1, # Meat 1, and # Chicken 1. Hope I make it clear,
View Replies!
View Related
Find Cell Value In Column & Return Text In Adjacent Cell
I have a drop down list in a merged cell B12F12 and B13F13 and B14F14 . . . B30F30. I need the adjacent merged cell to populate a reason (text) based on the text answer in the drop down list or the entered text in the first merged cell. For example in the cell B12F12 the user picks from the list or types in "Amiodarone." I want the adjacent merged cell G12J12to automatically fill with "Heart Rhythm." I also want to be able to set up multiple if  them statements like if Amiodarone is entered then fill adjacent cell with Heart Rhythm and if Toprol XL then fill adjacent cell with Heart / Blood pressure and if simvastatin then fill adjacent cell with Cholesterol, etc. I have about 30 different options for cell 1 that I want to have auto fill in cell 2 based on the contents of cell 1. I've attached my file. I want the user to be able to choose from the list or type the drug name in.
View Replies!
View Related
Sum Cell Numbers Which Are Preceded With Text
I have a number of cells which contain information such as 'H8', 'S4' and 'T6', etc, etc. What I want is a formula which sums just the numerical part of these cells, so would add up the 8 plus 6 plus 4. I have hundereds of these cells filled out like this. The first character in the cell is always a letter and then the number will either be a singe number (e.g. H8) or decimal (e.g. H7.5).
View Replies!
View Related
Sum Based On Adjacent Column Value
I have a spreadsheet with a list of account numbers and values(sheet1) and on a seperate sheet (sheet2) a list of all unique account numbers that appear in the transaction data sheet. What I am trying to do is get a total sum for the values of each account number. What I am getting well I'm not quite sure what I'm getting. The problem appears to be when the xSubtotal variable is reset to 0 at the end of calculating all the values for the account number.
View Replies!
View Related
Count Cell Characters & If It Exists Already
I have a worksheet (SHEET1) where users would enter their ID Number and then they would hit a Submit button. The submit button would then add that ID Number into another worksheet (SHEET2) in a running list of ID Numbers. But before it is submitted, I would like to check for two things: 1. ID Number must be 9 characters long (numbers and/or letters) 2. ID Number must not already exist in SHEET2 If condition 1 is not met, then just have a message box asking user to check their entry and try again. If condition 2 is not met, then just have a message box saying "thanks" but no addition will be made to SHEET2
View Replies!
View Related
Conditional Format  Compare To An Adjacent Column Of Numbers
I have a column of numbers (column A) which I'd like to compare to an adjacent column of numbers (column B) and, by row, determine if the number is larger. If it is, I'd like to place a different colour on the cell. What is the formula I need to use to accomplish this? Ex: Col A Col B 30,000 5,000  A is larger than B, therefore A is formatted RED 20,000 20,000  A is the same as B, therefore no format applied 10,000 15,000  A is less than B, therefore A is formatted GREEN
View Replies!
View Related
Multiple Ifs (checks Wheater Or Not In Column Exists Text)
I have an excel worksheet that creates the total arival/departure time for workers. In the "ore lucrate" column i need a formula that checks wheater or not in column b exists text, if it does then check if cell r12 is bigger than one hour and the substract exactly one hour from it, i.e. 03:45 needs to be 02:45. However if it's not bigger than an hour then put the result as it it (r12). And finally if the adiacent cell in "ore lucrate" doesnt contain text then it should be left as the folowing 00:00.
View Replies!
View Related
Conditional Formula: If Text, Repeat As Number To Sum, Else Sum Numbers.
I can get only so far, then stumped:3 columns (Hours, Rate, Amount). "Hours" is a 'List' with data from from another sheet. Data is named 'Worked'. 'Worked' is all 2 decimal numeric (represents total time worked), except first item called "Live In" IF 'Hours' is "Live In", I can use: =IF(A1="Live In", SUM(B1*1)) How can I make it conditional so that if it's not "Live In", then it will SUM(A1*B1)?. SideNote: 'Worked' LIST has total time with minutes expressed as 1/4 of hour (i.e., 1.25 = 1 hour, 15 minutes).
View Replies!
View Related
Formula Dependent On Text In Adjacent Column
I have a datatable which has a column containing up to 25 different text codes (which are currency pairs). I need to insert a specific number (as seen in the attached example) in a second column. This number depends on the text in the first column. I suspect this is very simple but at the moment, not for me!
View Replies!
View Related
Highlight Cell If Same Value Exists In Another Column
I'm trying to do a formula without using a marco. The formula would do the following:  Highlight value in column A if the value is found in column B and on the same line value in column F and I are equal to zero;  Value in column A could match many value in column B I tried everything I know but can't seems to make it work.
View Replies!
View Related
Count Specific Text For Percentage
I have attached an excel sheet and I want to count and report frequencies the amount of X that are occurring from the 'CSR1' book in a table in the 'Frequency' book. The CSR1 book has 4 sections and I need to formula to adjust the total percentage accordingly if only 1, 2, 3 or 4 sections have been populated with data. Please see attached.testdata.xls
View Replies!
View Related
Sum Formula: Sum Of The Numbers In Column
I would like a formula for cells B1, B2, & B3 that will give me the sum of the numbers in Column B which are next to their corresponding plan in Column A. The attachment is a sample of a sheet that will be having approx 500 rows to work with. I have tried using the "Sumif" funtion, but I can't seem to get it to work correctly.
View Replies!
View Related
Determine If Cell Value Exists In Column Of Table
Im trying to write a formula that will check if a name that I have in column A also appears in column B and if it does I want it to return a value for example TRUE in column C. I'm currently doing a survey and I have all employees in column A (600persons) and then column B I enter thos that have taken the survey. So If an employees name exist in column B he/she has done the survey and then they I get the value "TRUE" or "1" or something else. So in this way I can easily see who has and who hasn't done the survey. I've been trying with VLOOK and so on but I can not get it right? Maybe I need VBA?
View Replies!
View Related
Sum Cells Until Adjacent Cell Meets Date Condition
Two Sheets: 1) Top Sheet 2) IRA  Cells A4:A10 of sheet "Top Sheet" contain dates. i want B4:B10 to show the IRA value as of the date in column A: ____A___________B______________________ 4  09/22/2005  $ Total as of date in A4 5  10/06/2005  $Total as of date in A5 6  10/20/2005  $Total as of date in A6 7  11/03/2005  $Total as of date in A7 8  11/17/2005  $Total as of date in A8 9  12/01/2005  $Total as of date in A9 10 12/15/2005  $Total as of date in A10  Cells A6:A12 of sheet 'IRA' also contain dates but not in the same sequence (some date ranges had several transactions), B6:B12 of sheet 'IRA' contains the transaction values during the date range: ____A___________B______________________ 6  09/22/2005  $100 7  _ _ _ _ _ _ $200.................................
View Replies!
View Related
Count Number Of Rows That Have Numbers And Divide Sum Of Cells
I have a 52 (one for each week) page workbook. I am trying to average cell J9 for the entire 52 pages. However the information is not added to the cell until the end of the week so week 3052 all have 0s and should not be counted in the dividing number. Is there a way to have Excel count the number of cells that have a number (not 0) and divide the sum of the cells but that number. =(WK1!J9+.....WK52!J9) / (counted number of cells not = 0)
View Replies!
View Related
Sum & Count Of Unique Numbers Meeting Criteria
I have a list, 50000 rows long with phone numbers and the service provider it belongs to. I want to extract some of this information. I'm looking for a formula which can give me the following: How many unique phone numbers is there in column B from service provider number 4 and 5? The following pic is just an example of how the list is compiled. How can I get sum up only the numbers from 4 and 5 and not the whole bunch? If I use this formula Try this formula. Array formulas will have curly brackets {} emcompassing the ENTIRE formula. You do not add these, you Enter the formula with Ctrl+Shift+Enter and Excel will add them (details: Array formulas). Many more Excel Formulas =SUM(N(FREQUENCY(D1:D51112,D1:D51112)>0)). I sum up all the unique phone numbers, but I don't know how to set a "if service provider is 4 or 5" if you get what I mean. This was the first part. The second part is finding out how many times each unique number from service provider 4 and 5 is listed.
View Replies!
View Related
Delete Row If Value In Cell Exists In Another Worksheet Column
I have working data on list sheet and added data on compiled sheet. I need to match duplicates and delete these rows from compiled sheet. I've tried coping to the list sheet and use "Remove Duplicates", but it rids me of the data that has been processed and has fills applied. I'm looking for a macor that can do this work for me without losing data on list sheet. This data reaches into the thousands and I'm looking to run this off the CASE column.
View Replies!
View Related
Sum Specific Cells Within 1 Column
I am trying to sum specific cells withing 1 column based on a character within another column. For instance: A1 = X and H1=1000 A3 = null and H3 = 50 A5 = x and H5 = 100 I want to sum everything in Column H where column A only contains an "x". In this scenario the sum would be 1100.
View Replies!
View Related
Extract Numbers With Specific Text From Right Or Left
i use this code to get the value from the cell that contains "Ink"., and i got the codes from reading other problems: =IF(SEARCH("Ink",a1),LOOKUP(99^99,("0"&MID(a1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},a1&"0123456789")),ROW($1:$10000)))),"")+0 like this in a1 > Ink 253.00 and totally working! but the problem is if the word "ink" in the left of the value > 253.00 ink and the result is #NA, is there any way that i can get the value whether the word Ink is in the left side or right side of the value? also bothered why is it if the word is not "ink" in the cell and return > #value since i put ("") in the last part of If function(value if false)?
View Replies!
View Related
Sum Numbers In Column If Date In Other Column Matches
I have a spreadsheet which will be completed by numerous users, with a worksheet reserved for each area. The spreadsheet is to record the number of days lost to training etc on a weekly basis. Each worksheet has 3 columns – column A DESCRIPTION, column B WEEK COMMENCING DATE and column C DAYS LOST. The table will be completed by the manager’s as the info becomes available to them. I will be collating the data on another worksheet and need a formula that will look in column B for all instances of 01/10/07 and then sum the corresponding cells in column C, then do the same for 08/10/07 and so on. I have attached an example of a page. I thought it may be VLookup or Sumif, but I don’t know how to go about it.
View Replies!
View Related
Minus Number From Cell And Result In Specific Cell And Specific Column
I have number in cell (A1) = 100. when I enter number in any cell of column(B) for example (B1)=10. then in cell (C1) the result of (A1)  (B1) = (90) and if I add in cell (B2)=10 then in cell (C1) the result of (A1)  (B1+B2) = 80 .accumulatively in cell (C1). and any number in column (B), the result will be (A1) minus any number in column(B) accumulated in (C1) second question I have number in cell (A1) = 100. when I enter number at cell (B1) = 10 then the result would be in the adjacent cell (C1) = (A1)  ( B1) = 90 and If I enter a new number in cell (B2) = 10 then the result would be in the adjacent cell (C2) = (A1)  (B1+B2) = 80 and If I enter a new number in cell (B3) = 10 then the result would be in the adjacent cell (C3) = (A1)  (B1+B2+B3) = 70 and so on. I want the result to be add automatically to adjacent cell in column (C)
View Replies!
View Related
If Column A = 0 Then Adjacent Cell In Column B Divide By .3
Im wondering if this can be done in excel.. 2 columns if the value in column O=$0.00 then I need to divide the adjacent cell in column L by 0.3 or multiply by 30% this is the way I was trying to do this... =IF((O2:O6722)=0,DIVIDE(L2:L6722).3) I have tried it several different ways and cannot get it to work  is it my formula or can this even be done because I can't reference just one cell?
View Replies!
View Related
Looking Up A Sum Of Data In Several Columns For Specific Values Of Another Column
=(SUMIF($J:$J, "Player1",$K:$K)+SUMIF($L:$L,"Player1",$M:$M)+SUMIF($N:$N,"Player1",$O:$O))SUM($I:$I) This looks up if Player 1 was in the top 3 scoring zones and adds up the players total score. I have another column in this table of data labelled 'Number of Players'. I was wondering how to lookup the total score for this player in the games he played under 6 players for example and the games he played in where there were other six players. I've tried using that formula ^^ with variations of IF and SUMIF formulas to try and figure it out, but I keep getting formula errors or it just returns with the total score, and not just with the total score under 6 players. I am also trying to create a graph of date on the x axis against score to date on the y axis  everytime I try, the x axis is always numbers instead of the date?
View Replies!
View Related
Sum Numbers Within Text
Note: I have uploaded a sample spreadsheet with the problem and the required results. I have text (eg. 1* to 15*) (* is a wild card) being entered in the following columns Q21:Q128 R21:R128 V21:V128 W21:W128 AA21:AA128 AB21:AB128 AF21:AF128 AG21:AG128 AK21:AK128 AL21:AL128..... The starting row for each above column is 21 and ends at 128. The text is different in every cell where text is entered, some cells will be blank. Each number (1* to 15*) appears only once on the spreadsheet are moved around. On the sample spreadsheet I have created a summary area in the left corner of the spreadsheet and this is where I would like the results to appear. I am using the following formula. The only issue is I need it work from 1* to 15*. Right now it only works up to 9*. An example is 11 and the result is in cell D9 on the sample spreadsheet. = SUMPRODUCT((LEFT(Q21:BK128,2)="9")*(MOD(COLUMN(Q21:BK128)COLUMN(Q21),5)<2))
View Replies!
View Related
Count Numbers Including Text Only
I need to count the number of entries made in an column provided it should exclude text values but it should count all the values which is combined of text and numbers ..... I tried count but it's only counting cells containing number....
View Replies!
View Related
Count Column W/blanks & Values Based On Specific Date
I am in desperate need of a function that will count a column of data where there are blanks and values based on a certain date that will also capture any data that is added after refreshing the table from Access. I have tried several functions but this is what I have: =(ROWS('TouchBack Detail'!$Q:$Q)*COLUMNS('TouchBack Detail'!$Q:$Q))+(COUNTIFS('TouchBack Detail'!$B:$B,'Nov TouchBack Summary'!B$1)). The result should be 3 but it’s including all other cells in the column that are not and should not be included in the refreshed table’s data (Table_TouchBack.accdb). I have attached the spreadsheet for review. The function is in cell B27 highlighted in yellow.
View Replies!
View Related
Getting A Sum Of Numbers From A Particular Column.
I would like to Enter in a Catagory Letter (A, B, C, D, or E) in Cell B3, and a starting index number in Cell B4 (the index should range from 3 to 21) and Ending Index Number In Cell B5 (the index should range from 3 to 21). Need to using functions to get excel to display the sum of the values in the appropriate Category column but only between the rows designated by the starting and ending index values. I am very lost and dont know where to start. I have tried a muddle of IF and LOOKUP and SUM functions to no avail.
View Replies!
View Related
Sum Text Formatted Numbers
Is it possible to sum cells that contain numbers converted to text? I used TEXT function to convert them because I needed to format them (to display three decimal digits, if number < than 0.05, three digits otherwise). But now SUM Formula doesn't work.
View Replies!
View Related
Match Text To Numbers And Sum
In column 1 are 220,000 words (that's a lot ), every words corresponds to a number between 1 and 13 in column 2. Now i want to recode texts (column 2) using the codes in columns 1 and 2. I could insert all text in one cell, or i could insert each word in one cell. Then I want to calculate the sum of the corresponding values (column 2) of all the words (column 1) for each time they occur in the text (column 3). In other words, i want to recode text to numbers and i want to calculate their sum.
View Replies!
View Related
