Sum The Totals On One Column, If Two Other Columns Are True
(Excel 2003 on windows XP.)
I need to SUM the cells in column J when column E has the word "yes" and the range N:R has a letter "w" in one of the columns. Only one of the N:R columns will have an entry on any one row and it may not necessarily be a "W".
I have Googled, and being a beginner I don't comprehend the result! Some people were talking about using an add in, but I won't be allowed to add software to our PC's.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
 Add Like Text Totals Of Columns At Bottom Of Column
 Selecting Totals From UNHIDDEN Columns Only
 Column Totals
 Comparing 2 Or More Columns And Returning A True Value
 Dynamic Column Sum Totals  Anyone??
 Find Identical Item, Look For True 2 Columns Over
 Pivot Tables And Counting True Fields For Two Columns
 Find The Matching Text TRUE/FEE Or FALSE/FEE In Columns
 Stacked Column Chart Totals 2007
 Running Totals In One Column With Data Added From The Columnn Before It
 Record The Total Items From Each Sheet In A Column Called All Totals
 Hide Checkbox TRUE/FALSE & Sum Values Adjacent To TRUE
 Solve Counting Result One Col If Criteria In Another Column True.
 Data Validation: If Previous Column Is Having A Value, Then Return FALSE Else TRUE
 Last Ocurance Of The Last Date Used For Each Month And Then Use The Cell Number To Calculate The Column Totals For That Month
 Count Of TRUE & FALSE And Assign 1 To True And Yes And 0 To False And No When I Total The Rows
 Cell B1 To Display True If Any Of The Values In Column C Match Otherwise I Want To Display FalseOR Function Confusion
 Column 9 To Be The Sum Of Columns 3, 6, 7, And 8
 Add Totals By Day
 Splitting One Column Into Many Other Columns
 Insert New Column After Every 2 Columns
 Combining Two Columns To 1 Column
 Sum Column If Criteria In Other Columns Are Met
 Insert A Column Every X Columns...
 Add Two Columns Together And Then Divide The Sum By Another Column
 Inset 2 New Columns In Next Available Column.
 Combine Two Dynamic Columns Into One Column
 Summe Column Depending Of Other Columns
 Split A Column Into Multiple Columns
 Hide Columns On Column Labels
 Average The Difference Of 2 Columns W/out A 3rd Column
 Unlock Columns If Column Is Not Blank In Any Row
 Create Separate Columns From One Column
 Unhiding Columns (column A That You Can't Click On!)
 Copy All Columns Data In One Column
 Split Data In One Column Into Two Columns
 Count Duplicates In 2 Columns, But Not If Column A=b
 Macro: Transpose 1 Column Into 3 Columns
 Transpose Each Row Of X Columns Into Single Column
 Calculate Totals
 Sum Totals By Date
 Adding Up Totals
 Sum Totals From Another Worksheet When (a And (b Or C))
 Tallying Totals Of Ten
 Add Totals By Week
 Calculating Totals
 Cumulative Totals
 Totaling Sub Totals
 Having Different Totals From Different Options
 Merge Columns B,C,D,E And F, If Duplicates Exist In Column A
Add Like Text Totals Of Columns At Bottom Of Column
Note that the "Fruit" and "Fruit Totals" column letter position will always be different every time I run the macro, so I would like the macro to find these columns by name rather than by column letter. I'd like a macro that adds the total number of fruits in the "Fruit" column by looking doing 2 things... 1. Add the total number of "Fruits" in the "Fruit" column, find the bottommost row of data in the "Fruit" column, go down 2 rows, and create the text named "Fruits Total" 2. In the cell directly to the right of "Fruits Total" put the value of the total number of fruits in this cell (which will be in the "Fruit Totals" column) 3. Add the total number of "Apples" in the "Fruit" column, find the bottommost row of data in the "Fruit" column, go down 2 rows, and create the text named "Apples Total" 4. In the cell directly to the right of "Apples Total" put the value of the total number of apples in this cell (which will also be in the "Fruit Totals" column) 5. Repeat steps 3 & 4 for the other fruits Best to show you an example ...
View Replies!
View Related
Selecting Totals From UNHIDDEN Columns Only
I have a workbook that has 2 sheets; "Quote Summary" and "ECS". The "ECS" sheet contains columns "C" through "L" each of which has a total on row "27". At any time, one or several (but never All) of these columns may be unused and therefore hidden. I would like to know if it is possible to sum the total of only those columns that are NOT hidden and display that value in cel "C15" of the "Quote_Summary" sheet.
View Replies!
View Related
Column Totals
I need help to total a column but in four cells the values are as a result of conditional formatting and the total ignores those cells. Is this difficult or am I a bit slow? This is in 2003.
View Replies!
View Related
Comparing 2 Or More Columns And Returning A True Value
I'm quite new to array formulas in excel and was wondering whether or not I can advance on this formula: =AND($C$5=B7,$K$5=G5) The problem is I would like to compare the entire colums (or array) and return the number of 'TRUE' statements in the array as a value. =AND($C$5:$C$65536=B7,$K$5:$K$65536=G5) however, this returns all values as 'FALSE' example of arrgument: IF C5:C65535=customer then count how many cells in K5:K65536=completed
View Replies!
View Related
Dynamic Column Sum Totals  Anyone??
I have a column of figures and a formula that displays the sum. My issue is that I occassionally insert more rows with more figures but obviously the sum doesn't include this new figure as the range is static. I guess I'm looking for a way to dynamically perform sums in a column.
View Replies!
View Related
Find Identical Item, Look For True 2 Columns Over
Im putting together a blackjack odds calculator. I could use some help with a formula. If I have an A,7 (ace low) it needs to return a 0 (or false) for a Possible Hand because further down the column A,7 (ace hi) has a true End Hand Total I was going to use an IF and a VLOOKUP (looking lower on the column), but then I cannot sort the column. Is there a formula that will find the hand and check for true 2 columns over from identical hand. There are 169 starting hand combinations. The attached worksheet only has 31 for simplification.
View Replies!
View Related
Pivot Tables And Counting True Fields For Two Columns
I have just started using pivot tables and I have ran into a problem with the count feature. I have an original data list that breaks down in the pivot table to: date events on that date Using the pivot table facilities, I want to do a count of those who said they were going to attend, and those who actually attended. The detailing both of these columns on the original list have a true false designation. When I do the count, the summarised information only counts the number of records for each of these events, and not the 'true' field entries for each of these columns. How do I count these two parameters for 'true' from the original list in the pivot table so I can see a comparison between these two numbers?
View Replies!
View Related
Find The Matching Text TRUE/FEE Or FALSE/FEE In Columns
I have 2 sheets. On one sheet, this column J2:J490 contains text that either says "TRUE" or "FALSE" and another column AJ2:AJ490, text that says "FEES". (many of the other cells in this column say something else). I need a formula that can find the matching text TRUE/FEE or FALSE/FEE in these columns, count them, and give me the product.
View Replies!
View Related
Stacked Column Chart Totals 2007
Would like to add totals to stacked column chart in Excel 2007. I have followed same steps listed here [url] but cannot position data labels above column. Excel only gives me the options Center, Inside End, and Inside Base. I also tried setting label position with VBA: For Each co In ChartObjects For Each s In co.Chart.SeriesCollection If s. Name = "Totals" Then For Each dl In s.DataLabels dl.Position = xlLabelPositionOutsideEnd Next dl End If Next s Next co Also tried the following without luck: dl.Position = xlLabelPositionAbove For each VBA example I get following error: Runtime error '2147467259 (80004005)': Automation error Unspecified error It seems like Microsoft has removed this positioning option for stacked column charts because it works fine for normal column charts. Anyone find a way to do this otherwise?
View Replies!
View Related
Record The Total Items From Each Sheet In A Column Called All Totals
I am a bit stuck with an excel spreadsheet I do for work and would like to ask your advise, I will try to explain as best as I can. I am running a small store that feeds 13 buildings. On the spreadsheet there are 13 sheets, one for each building. The items will go out monthly. What I need to do is record the total items from each sheet in a column called All Totals on the first sheet, this will add up the totals for all the buildings. If I put an attachment that may help out a bit. If you can look at P3 it is all the P3s to add together, next down will be total of P4,P5,P6.
View Replies!
View Related
Hide Checkbox TRUE/FALSE & Sum Values Adjacent To TRUE
I have a worksheet where I have around 300 rows, each with 7 columns. What I want to do is add a checkbox to each column. I plan on setting nonapplicable checkboxes to mixed status and locking the worksheet. I will unlock applicable checkboxes and sumif or countif their value according to rowbased scoring, for example, each checked checkbox represents a value of 3. I do not know VBA and have chose to use the form control checkboxes rather than ActiveX. I believe that a formula for this would be something like: =SUMIF(B1:B3,True,"3") or =COUNTIF($B$1:$B$3,True)*3 I am wondering firstly if I have that right and secondly if there is a way to stop my checkboxes from displaying labels. Currently, if I click on one it displays True behind the active checkbox. If I uncheck it, it displays False.
View Replies!
View Related
Column 9 To Be The Sum Of Columns 3, 6, 7, And 8
I need column 6 to show 15% of column 5, then I need column 9 to be the sum of columns 3, 6, 7, and 8. I just can't figure out how to do this. What I'm trying to do is take a wholesale price, get 15% of it to figure for shipping, then add the wholesale price, shipping charge, a drop ship charge, then my Paypal charge together so I can see what I am paying to ship a specific item...then I can figure out what to charge.
View Replies!
View Related
Add Totals By Day
Here's what I have... Column A Column G date $ Amount my data example... 10/5/2008 $10.00 10/5/2008 $20.00 10/8/2008 $12.00 10/8/2008 $8.00 10/8/2008 $25.00 10/9/2008 $75.00 What I want the formula to do is look in Column A find all of the dates that are on the same day and then look in column G and add all of those $ amounts. So the result would be... 10/5/2008 $30.00 10/8/2008 $45.00 10/9/2008 $75.00
View Replies!
View Related
Splitting One Column Into Many Other Columns
I need a macro which place chosen parts from one column into another column. For example I have following text in column E: Column E Flugschneise {f} ringsum (total) im Arsch [vulg.] mutterseelenallein Geld und Gut Pensum {n} *chosen content not always at the end of a cell! Now i need a macro which puts chosen content from Column E to Column F or Column G. For example I want that all {f}s should be deleted from column E and put into column E. Or I want all {n} to be deleted and placed into column F. Or I want all [vulg.] to be deleted from column E and put into column G. If column F or G already consist of text then the new text( {f} or {n} or [vulg.] ...) should be added to the existing text but separated through a separator like comma or semicolon
It means that the macro is each time fed with information about what to delete from which column and where to put it. It would be cool if a simple msg box would ask for the needed information.
View Replies!
View Related
Insert A Column Every X Columns...
I am looking for a VBA code (or alternatively a way of doing this in normal Excel) that can let me insert a column every X columns (for example every six columns). Ideally I would want a code that can let me insert a specific column (the same) every X columns, but a formula that inserts a blank column every X columns will hopefully do.
View Replies!
View Related
Inset 2 New Columns In Next Available Column.
I have had a search around but cannot find exactly what I'm looking for. I have a worksheet with data from B5E10 and G5J10. I need a macro which will insert two columns after column E. So then it would push what was in G5J10 to I5L10. The macro would need to look for the blank column (column F) and then insert two columns before it. The columns will be constantly changing so that why it needs to look for that blank column. After they have been inserted would it be possible to give the columns headings such as 'New column 1' and 'New column 2' in row 4.
View Replies!
View Related
Summe Column Depending Of Other Columns
I want to create a macro that make the somme of 2 other columns when the nummer of the first column ist double(more than one ) and put the somme in a new Rows. like this : A B 23 5 23 12 23 2 45 4 45 1 46 3 in the news the resultat in the same columns it΄s like this A B 23 19 45 5 46 3
View Replies!
View Related
Split A Column Into Multiple Columns
The spreadsheet contains over 21,000 rows of data, and one of the columns (D I think) contains data as in the two examples below. What she wants is to split this column at the semicolons ( and have the column header as the "field" name. Unfortunately not all the cells have the same number of "fields" as you can see. Some don't have an "addressLineTwo" while others also have "stateprovince". Is it possible to split the column so each "field" goes into it's own column? Please note that if a "field" is missing there is not two semicolons to indicate an empty field. I'm also fairly certain that, between them the two examples below show all possible fields. Data Examples. addressLineOne:Road Belen Staana;addressLineTwo:Costado Oeste;city:SAN ANTONIO DE BELEN;highRate:194;latitude:9.97631;longitude:84.20038;postal4005 addressLineOne:1766 Homestead Drive;airportCode:ROA;city:HOT SPRINGS;highRate:500;latitude:37.99662;longitude:79.83079;postal24445;Rating:52;stateprovince:US Didn't there used to be a "Split" function that split text over two cells? I'm sure I used it years ago, but can't find any mention of it in Excel 2003.
View Replies!
View Related
Hide Columns On Column Labels
I was wondering how I might be able to hide columns based on their 1st row labels with vba  I never know where these columns might be on different file I get but I want to hide the same columns every time. right now I am just specifying column numbers or letters but it's not working out: Columns("A:C").EntireColumn.Hidden = True Columns("G:I").EntireColumn.Hidden = True Columns("K:R").EntireColumn.Hidden = True Columns("T:V").EntireColumn.Hidden = True ActiveSheet.PrintOut Columns("A:C").EntireColumn.Hidden = False Columns("G:I").EntireColumn.Hidden = False Columns("K:R").EntireColumn.Hidden = False Columns("T:V").EntireColumn.Hidden = False
View Replies!
View Related
Create Separate Columns From One Column
I have a spreadsheet with these columns:Group_Name Contact Name Company Name Contact ID Situation: The Group Name Column has the name of the group that the Contact Name is a member of. The Contact Name could be a member of many Group Names and therefore there may be many rows of the same Contact Name one with each Group Name that they are a member of. I have manually created separate columns for each Group Name in the spreadsheet that is attached. Goal: I would like to have a macro that will look at the Group Name column and create a new column for each distinct group. I then would like the macro to go down the Contact Name column (I believe that I will have to have it sorted)and look at the Group Name that is listed in that row and put "True" in the Group Column that was created in the above step. Then, go to the next row, if the Contact Name is the same, look that the Group Name column and put "True" in the corresponding Group Column in the FIRST ROW OF THAT CONTACT NAME. Then Delete that row.
View Replies!
View Related
Split Data In One Column Into Two Columns
For each cell in column A, I am trying to split the data between two new cells. The data in each cell is separated by a . All information to the left of  should go in column B. All information to the right of  should go in column C. Column A Aa3 AAA A3 AA A2 A A3 BBB+ Aa3 AA Aa2 AA+ Desired Results Column B
.Column C Aa3
AAA A3
..AA A2
..
A A3
..BBB+ Aa3
AA Aa2
AA+
View Replies!
View Related
Count Duplicates In 2 Columns, But Not If Column A=b
I have data in two columns where column A is a name and column B might be a synonym or might just equal the value in column A. I want to count duplicates if cell A2 matches any other cell in column A or B, unless the only match is with cell B2. If cell A2=B2, but also matches cell A7 then it should be counted as a duplicate. I've searched through a lot of the threads and found a lot of useful information, but nothing that seems to do what I need.
View Replies!
View Related
Calculate Totals
I have a spread sheet that is used to review calls placed by a call center. Column A has the extensions of the phones, and Column E has the type of call (Outgoing or Incoming). Each line is a new phone call. We have about 8 extensions, but the worksheet could have a couple thousand calls. So, Column A could have extension 1401 from rows 1100 as extension 1401 made 100 calls. I'm looking for a formula or macro that will summarize how many outgoing and incoming calls extension 1401 had. Thoughts? My initial thought was something like this: =SUMPRODUCT((A2:A5000=H2),(E2:E5000)) In this formula, I would type in the extension of H2 and it would scan Column A and add up the values in Column E. The only problem is, that Column H doesn't contain a numerical value. It only has "Incoming" and "Outgoing" (minus the quotes), so this doesn't work.
View Replies!
View Related
Sum Totals By Date
I would like to get the total in column B but its not giving me the correct total. So I would half to use a helper column in column D to retrieve correct results. Is there anyway I could have an all in one formula for this. I would prefer not to use the helper column. The correct result is in cell D23.
View Replies!
View Related
Adding Up Totals
Im trying add up totals on a list a1b2c3a4b5c6a7b8c9a10b11c12a13b14c15 So for instance i want it to look at column A and find all the a's then then add the value of column b so the total here would come out as 35
View Replies!
View Related
Sum Totals From Another Worksheet When (a And (b Or C))
I need and can't wrap my head around it enough to figure it out myself. On one worksheet, I have raw sales data: A B C D E F Cust # Cust Name Product # Prod Name Sales Date 10001 whoever 800001 whatever 200 1/3/2009 10003 whoever2 800241 whatever 250 1/3/2009 10001 whoever 800060 whatever1 210 1/3/2009 10001 whoever 800055 whatever2 222 1/3/2009 Each product has multiple product numbers, so one product could have multiple product numbers: 800001, 800055, 800241, 800002, etc.. I want to count total sales by product, regardless of customer for a certain date. I have the product numbers in their own cells. I would like to say "If the product number is any of these, and the date is this, add all the sales numbers together and give me a total"
View Replies!
View Related
Tallying Totals Of Ten
I have a column of wickets (M). M5 and M6 are the number of wickets for one match, M7 and M8 for the next, and so on. Two wicket entries for each match. I needed a formula to count the amount of times the combined wicket total for a match is greater than 10. Initially there were only a few M values, so I used this: IF(M5+M6>=10,1,0)+IF(M7+M8>=10,1,0)+... and so on. Now I want to expand it to have more M values and this formula would become huge.
View Replies!
View Related
Add Totals By Week
Does Excel know week numbers? e.g. Week 40 is Sunday 10/5/2008 to Saturday 10/11/2008. Here's what I have... Column A Column G date $ Amount my data example... 10/5/2008 $10.00 10/5/2008 $20.00 10/8/2008 $12.00 10/11/2008 $8.00 10/12/2008 $25.00 10/13/2008 $75.00 What I want the formula to do is look in Column A find all of the dates that are in week # whatever and then look in column G and add all of those $ amounts. So the result would be... Week 40 $50.00 Week 41 $100.00
View Replies!
View Related
Calculating Totals
I am attempting to build a spreadsheet for work, and I'm having trouble calculating a correct total. In essence, the bit of my spreadsheet I'm concentrating on is 2 columns: Column A is headed "Type of Work" and Column B is headed "Completed". The Type of Work will either be Letters or Memos, and the Completed will either be Yes or left blank to indicate No. For other reasons, No has to be represented by a blank cell. At the moment, I have 12 rows, going from A2 to B13, with data such as the following: Letters Yes Letters Yes Letters Letters Letters Yes Letters Letters Yes Memos Memos Yes Memos Memos Memos Yes I need a summary section at the side with the following calculations: Total Number of Entries Total Number Outstanding Total Number of Letters Outstanding Total Number of Memos Outstanding However, as I have only populated it with 12 rows of data for my testing purposes, I need to build formulas to take into account the fact that a maximum of 2000 rows may be filled in by other people over the next few months. Therefore, my formulas look like the following: Total Number of Entries (stored in E2): =COUNTA(B3:B2000) Total Number Outstanding (stored in F2): =E2(COUNTIF(C3:C2000,"Yes")) Both of the above formulas work perfectly, and when I enter a 13th and 14th row, the totals update as I want them to, so I have no problems there. However, I am struggling to enter a correct formula which will calculate the total number of Letters that aren't Completed. I did set some names and tried to use: {=SUM((Type="Letters")*(Completed="Yes"))} but it gave me a #NA error, presumably because the named range was looking at all the empty cells up to A2000.
View Replies!
View Related
Cumulative Totals
This problem has come at the end of a big exercise whereby I've managed to construct a spreadsheet that automates three payment processes based on various VLookups to another file. However, I've come to a stage where I need to preempt a payment allocation based on cumulative totals. I'll try and explain clearly below but I've also attached a spreadsheet showing the intended result (along with a copy without the output so that someone can add in the formula  again, if it exists. So, this all centres around a code allocated to our clients and a declaration that they complete. Say client code "Apple1" (col E) sends in instructions to pay on a position of "650,000" (col F). Within our existing spreadsheet we have an available position of "800,000" which can be seen by totalling column G for client code "Apple 1". So that's the first requirement  for all rows on my existing spreadsheet for client code "Apple1", I want to add the cumulative total of column G ("800,000") I'd like this captured in column H, as per my example. We can then make payment based on their declaration, up to their total available amount. However, we need to pay them in stages according to our existing allocations (col G). Therefore, taking the first example, their declaration show's an available position of 650,000, their total position is 800,000 and the available nominal amount for that row (col G) is 200,000  so they can be paid on that full amount therefore, 200,000 should be shown in cell I3. So now, they've got 450,000 left to be paid and cell G4 again shows a position of 200,000, so once again, cell I4 should show 200,000. The client "Apple1" now has a payment amount remaining of 250,000 remaining but in this instance, cell G5 is for 300,000  so because the remainder of their available payment is less than the amount in cell G5, we should post the available payment amount in that field  250000. Therefore, if you add up all of column I for client "Apple1" it comes to 650,000  the amount we have on our declaration  even though the total of their available position is 800,000. I've included another client in the mix "Sauce2" who should show 200,000 and 0 respectively in cells I6 and I7.
View Replies!
View Related
Having Different Totals From Different Options
I would like to know how do I go about adding the following: This is a test it counts out of 1250 points But there are 3 fields which can be omitted from the grand total of 1250 Some students might not have all three fields. The fields totals are 30, 90 and 130. The students might have one or two of them in either order. Now what I would like to do is have 3 different fields where I can mark with a Y=Yes and N=No in a block. then it would change the Grand Total score accordingly.
View Replies!
View Related
