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:
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
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
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
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
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
Create Sub Totals And Sum Total
Attached is an example of the workbook I am looking for: A macro will take the sheet "Data" and split the info across into new separate worksheets labelled "SIM","MOR","RED","CAR","EMB". The split worksheets contain the same info as "Data" however the column for "Total Arrears" is sorted as descending and the moment values become negative, there is a 5 row gap to seperate the Positive amounts and Negative amounts in a descending order What needs to be completed: I need to create Sub Totals in columns E:K for both the negative and Postive balances (and Label them as "Sub  Total" in column D) as well as have them represented as a percentage, and then after that have a grand total value represented at the bottom. I have attached examples of what the final product should look like for each worksheet (they are labelled in blue) The problem I have is that I am unsure of how to use VBA to recognise where to put the amounts for Sub Totals. I would assume it would use a .End.xlUp.Row and some kind of Offset, but I don'r even know where to begin. The "Data" sheet has a button that runs the macro to split the data into worksheets, but the worksheets SIM/MOR/RED/CAR/EMB must be deleted first since there cannot be duplicate sheets (which is why there is also a button to delete those sheets)
View Replies!
View Related
Sum Pivot Table Totals
I am using a database sheet to log pupil misdemeanors, namely Lates, Disruption and Absconding. From this database a pivot table is generated from which a 'hit list' of the worst 10 latecomers are compiled. The code that I have at the moment (see below) sums all misdemeanor activity by pupils rather that just values relating to lateness. I have included a sample file with expected output ....
View Replies!
View Related
Sum Totals In Bottom Toolbar
In the bottom right corner of a worksheet there is a place that allows for sum, average etc... to be displayed for a selected goup of cells. Is there a way to make that show a cells value as changes are made to a worksheet? EDIT: I guess I do not have to do it this way. I am trying to figure out how to keep a total on the screen at all times and I have freeze Panes already in use.
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
Auto Lookup And Sum Month Totals
I have a table which holds a grid of data (see attached spreasheet) On the next sheet I have a table which I polulate with the sum of the month for each column. I need a way to auto populate these based on the data present. There will never be more than 365 days worth of data however this data could fall between any dates. I need the second sheet to firstly populate itself witht the months which fall within the date range, then to sum the relevant column for each given month.
View Replies!
View Related
Sum Column Based On Other Columns Year & Month
I have the following variables in these columns Column 1: Ship (1064, 1065, 1066 as the field contents) Column 12: Date (21Feb08 as format) Column 13: Weld Length (1000 as format) Column 15: Defect Length (1000 as format) What I need doing is the following is in a single cell per month add up what the total weld length is as well as the defect length as I have Jan 08, Feb 08 etc on another sheet where these values will be returned. There is a seperate sheet for each Ship so would like a formula that I could ammend 1064 to 1065 etc
View Replies!
View Related
Sum Of 3rd Column Values Based On Nested If On 2 Other Columns
I have three columns as follows: ItemsWeekEnding Count a14/7/2007 10 a34/7/2007 3 a14/14/2007 11 a14/7/2007 5 a44/7/2007 6 a14/21/20078 a44/14/20072 a34/21/20071 a24/7/2007 6 a14/28/20077 a24/21/20074 a94/28/20079 I want to Sum Count values (column 3) for matching WeekEnding (Column 2) and Items (Column 1), example: a1 4/7/2007 15 as a1 for this weekend is occuring twice. I need the formula to do it in excel.
View Replies!
View Related
Sum Alternate Columns Base On Previous Column Entry
I am trying to resolve a calculation issue where I want to sum accross columns depending on an entry in the column immediately preceeding. The layout is an Attendance sheet, The columns are for the days of the Month ( 1  31 ) and the rows are the Months. There are 2 columns associated with Each day. The first column is for the type of Time Off ( Vacation, Sick, Personal, etc ) the column next to it records the number of Hours some one took off. The work book has a Sheet for Each Employee and a running total needs to be maintained for the amount of "off time" each employee takes by the various time off categories. I have tried setting up range names but this won't work as there will be multiple sheets. I believe the problem is the mixture of Text and Numeric data but could not resolve.
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
True Or False To Disply As A Sum Total?
Hi, I am doing a spread sheet for work and just have a little issue. I have two time fields in the spread sheet and if one is less then the other it reads true and turns green but if it’s greater then it reads false and goes red. Is there a way of it to say how much the time is under or over the original instead or just reading true or false?
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
Sum Values If Multiple Variable Criteria Are True
I'm trying to find the sum of a range of values based on multiple criteria, and the criteria is that the fields all have to be identical, then sum them. I've attached a brief example spreadsheet that has the fields A=City B=State C=Values What I want the formula to do is first find the range of all the matching states, then find the range of all the matching Cities within the states, and then sum the values based on them having matching city values. I've been able to do that with one criteria using SumIf, I'm not sure if this will help paint an image of what I want to do: =ROUND(SUMIF($B$3:$B$11,$B$3:$B$11,$C$3:$C$11),0) But I can't figure out the way to do multiple criteria against itself. Most of the results I get from Google using multiple criteria are using a set few values, and I can't seem to figure out how to alter those methods to work with my situation.
View Replies!
View Related
Find Identical Item, Look For True 2 Columns Over
I’m 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
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
Sum Up The Totals Of 'quantity X Unit Price' For Each Product
i am currently working on cost analysis of large projects. I have a worksheet with about 100 headings and i would like to sum up the totals of 'quantity x unit price' for each product. I tried the sumproduct function but it did not work and i had to do the '=sum(b4*c4,d4*e4,f4*g4,..........) to get the result in the total column. I attach a small sample of the file.
View Replies!
View Related
Macro: Check CheckBox Is True, Current Date For Day/Month, Then Sum TextBox & Cell
I am trying to allow the Command Button when clicked to go through multiple conditions before making a decision. So, when someone clicks on Command Button 3 the code should look to see if CheckBox1 is true, then it should check today's date, and if it is between a range of days, or even months, then it would add the number in TextBox1 with the amount already in cell H18. This event will happen every time someone clicks on the Command Button. The end result is to have several sheets (4 total) for each quarter in the fiscal year, and if the dates are within those parameters, the clicking of the command button will update the correct sheet.
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
Check One Column, Get Value From A Second Column And Display Sum In 3rd Column
I am tying to total all the sales for a given customer. This is what I have at present Customer Sales CustA 1000 CustB 500 CustA 1250 CustC 750 Elsewhere in the spreadsheet I have the following Customer Total Sales CustA CustB CustC As you can probably see I need to get the sum of each customers sales and display this under Total Sales. I thought about using VBA but am not really sure where to start. If anyone has any ideas how I could progress this I would be most grateful. I am using excel 2007
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
Function To Sum A Column's Entries Based On Another Column
Let's say I have a dataset with blank or X in column A, and a dollar value in column B. I want to sum the dollar amounts for column B only if there is a corresponding X in that row in column A. The way I currently do that is by creating a new column C, making a if statement to display the value of B only if there is an entry in A, and then doing operations on that new range. I figure there has to be an easier way. I don't know how to use VLOOKUP, despite trying to read the helpme a bunch of times. Is that relevant?
View Replies!
View Related
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
Finding The Sum For Values In One Column That Are Connect To A Value In The First Column
I have two columns. One column has UPCs  some of which are duplicates. The second column just has number values. I'm trying to add the sum of all of the numbers in column two which are attached to their respective UPC. For example, COL A///// Col B 11111111111///// 10 00000000000///// 15 11111111111///// 10 11111111111///// 4 00000000000///// 2 So, I need a third and fourth column to give me the total value for a single SKU(col A) of all the values in col B. In this example the Third column would contain the SKU, and the fourth column would contain the sum of all values in column B that are associated with the single SKU in column three. The third and fourth column would look like this: COL C///// COL D 11111111111///// 24 00000000000///// 17
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
Sum Depending On 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 C 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 C 23 19 45 5 46 3 I got this code , but I work only for news colums , A and B Sub test() Dim a, i As Long, b(), n As Long a = ActiveSheet. Range("a1"). CurrentRegion.Resize(, 2).Value Redim b(1 To UBound(a, 1), 1 To 2) With CreateObject("Scripting.Dictionary") .CompareMode = vbBinaryCompare For i = 1 To UBound(a, 1) If Not .exists(a(i, 1)) Then n = n + 1: b(n, 1) = a(i, 1) .Add a(i, 1), n End If b(.Item(a(i, 1)), 2) = Val(b(.Item(a(i, 1)), 2)) + Val(a(i, 2)) Next End With ActiveSheet.Range("d1").Resize(n, 2).Value = b End Sub
View Replies!
View Related
Repeating Sum Of Several Columns
I have several columns of data representing a shop operation along different years. Now I try to see how will it look like if I have 10 shops like this, all summed up. The problem is that each shop will have the same numbers as the other 9, just that it's first year of operation will be different. Now, I don't want to have different sheet for each and every shop since their data is exactly the same. I only want to have 1 type of shop which I can add to a consolidated sheet but 10 times, and each time the first year shoud be different. Just an example: If I have this data for my shop: YR1 YR2 YR3 YR4 Sales: 10 12 14 16 Costs: 5 6 7 8 Operation: 2 2 3 3 Tax: 1 1 1 1 profit: 2 3 3 4 And lets say I have 4 shops opening in 2000,2001 and 2 in 2002 than my consolidated should look like this: 2000 2001 2002 2003 Sales: 10 12+10 14+12+10*2 16+14+12*2 Costs: 5 6+5 7+6+5*2 8+7+6*2 Operation: 2 2+2 3+2+2*2 3+3+2*2 Tax: 1 1+1 1+1+1*2 1+1+1*2 profit: 2 3+2 3+3+2*2 4+3+3*2 But again, I don't want to open a different table for each shop and than sum them up  I want to have only my one shop, and have a possibility to say when will I open my next shop and the consolidation will be automatically.
View Replies!
View Related
{Sum(if(... )} Applied To Whole Columns
I cannot acheive to have a {Sum(if(...)} formula applied to whole columns. See below. The following formula return a numeric value. So it works. {=SUM(IF(Query!$L1:$L65535=TEXT($A37,"@"),Query!$AM1:$AM65535,))} (confirmed with Ctrl+Shift+Enter) The following formula returns #NUM. So it fails. {=SUM(IF(Query!L:L=TEXT($A37,"@"),Query!AM:AM,))} (confirmed with Ctrl+Shift+Enter) how to use {Sum(if(... )} to whole columns?
View Replies!
View Related
Formula To Sum Three Columns
I have a spreadsheet with a unique key for a customer in column A, and then I have the data by month in columns BD. Some customers have multiple lines, so I have used a sumif to sum for that customer. My question is this: I would like to know if there is a way to write a formula such that the value returned is the sum of all three columns (BD) so I would see the total for a customer for the quarter. I can get around this by writing three sumif formulas, but I figure there must be an easier, simpler, way.
View Replies!
View Related
