{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 Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Sum Filtered Data After Filter Is Applied
I have a spreadsheet that I want to sum the data similar to SUMIF or SUBTOTAL after a filter is applied. So I have data in column "F" that I appliy a filter and based on that filter I want to sum only the filtered totals in column H. Is there an easy way or is creating a new function the only way to go?
View Replies!
View Related
No More New Fonts May Be Applied To This Worksheet
I have a very simple workbook in Excel 2000 with one page containing all my data which I update on a daily basis. Some of the data is linked to another workbook so that it updates each time I open it. Then I have 12 tabs, one worksheet for each month of the year with 10 simple graphs on each worksheet. As you can see, it is a very simple worksheet. Each month, I copy and paste the graphs from the previous month into the current month and simply alter the Source Data in the graph to gather the current month's data. At the start of this month, it allowed me to copy and paste, but when I tried to alter the Source Data to the current month, I got a window on screen with a yellow triangle containing an exclamation mark and the words "No new fonts may be applied to this worksheet". If I click the OK button or the cross, it responds but the whole programme is locked. The only way I can get out is to give the 3 fingered salute and close Excel down.
View Replies!
View Related
No More New Fonts May Be Applied In This Workbook
I have a workbook with 33 worksheets and more than 150 charts. Now, when I rightclick on a chart to access "Source Data" for example, I get a message saying "No more new fonts may be applied in this workbook". Then I have to click "OK" about a dozen times before I get access to the dialog box. Curiously, it happens for some charts and not for some others.
View Replies!
View Related
Error On Function Applied To Array
I have the following code (below) which calls a function to return the sum of values in a specific part of the array. I haven't set the function up correctly as it is causing an object not defined error(424), but I can't see where and have been going round the houses changing it. I'm wondering if it's my confusion over when to use By Val? Sub getDataIntoArray() Dim arrDataTot() Application.ScreenUpdating = False Application.DisplayAlerts = False Application.EnableEvents = False On Error Resume Next ReDim arrDataTot(1 To 1)
View Replies!
View Related
Rank Data With Subtotals Applied
I have a worksheet that I'm trying to rank program selling. The range has subtotals that I need to exclude, so I'm trying to rank over multiple ranges. I've thought about LARGE in an array function, but can't seem to make that work. Is SUMPRODUCT a possibility? I've attached the worksheet, trying to get the top programs (again excluding the subtotals in bold).
View Replies!
View Related
Validations Applied On A Cell Containing Formula
I have a problem in applying Data Validation on cells containing Formula. Eg: cell A1 has a value 10 and cell B1 has a value 15. i have a formula "=A1+B1" in cell C1. If i apply a Data validation to the cell C1 so as to accept values only in the range of 0 to 10, the cell C1 is still accepting values grater than 10 when i change the values in the cells A1 and B1.
View Replies!
View Related
Format>cells>date Not Applied
I can select an individual cell or range, select Format>Cells>" date" format, hit okay, and nothing actually changes unless I click on a cell so that it brings up the edit cursor. I can then hit enter (not changing the content of the cell in any way) and then Excel recognizes it as a date. The cell values are all in the standard "3/14/2001" format, with the exception that single digits are 0padded, and there are no spaces preceding or following the date string. The issue here is that without the cells being recognized as actual dates, they cannot be properly sorted, eg: the original range: 01/04/2005 01/01/2005 01/02/2004 01/03/2005 is sorted, incorrectly, to: 01/01/2005 01/02/2004 01/03/2005 01/04/2005 ...obviously for ascending order, the single 2004 date should be placed at top. Any ideas other than a custom datetext parsing macro? I could do that, but the size of each sheet, the number of sheets per batch, and the frequency of individual batches makes that idea completely impractical. ...Technically, my macro couldn't sort the above ranges correctly, but on further investigation, I noticed I couldn't do it manually either.
View Replies!
View Related
Specific Find Followed By Equation Applied And Outputted To A New Column
I have the data sheet, which is the result of another function. It produces 10 columns with numbers in each cell, there can be any number of rows, but the key is that the rows are outlined as follows. The first row has the first column number bolded, followed by the second row not bolded and then a space and a repeat of the bold cell, followed by the non bold cell. Each row fills up all ten columns. I have attached an example of the sheet. What I would like to do is create a function which says brings up a box that says "Input Row" and the user would input the bolded number of a row. From here the code would take that value entered and divide it by the inputted bolded number’s adjacent cell, to the right, aka B2 column. This value would then be multiplied by every other bolded rows second cell, B2 column, essentially giving rise to a normalization of that column. The output would then be displayed in a new column, column 11 in italics. Each row would now have its italics value corresponding to the bolded number’s row and not the other rows.
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 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
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
Conditional Sum Based On Two Columns
I have a little problem I would like some assistance on. I pull data from a data warehouse into an Excel spreadsheet that is linked to another spreadsheet that consolidates the data. I need to add the PGL Grads only for completed classes which are usually show zero (Actual Grads column) unless there were no entries (Actual Entries). Currently, the formula used is =SUMIF($I$104:$I$118,">0",$H$104:$H$118) but this can cause problems if there is a zero value for a completed class. In the example below, the total for PGL grads is 142 but in reality it should be 158, the zero value for class 2008008 skewing the result. I have tried variations of SUMIF, SUMPRODUCT, and SUM and end up with the same results. The warehouse data puts zero values in the Actual Grads column for classes that have not completed and I know I can make this work with blank values but that just creates another step in the process ...
View Replies!
View Related
Loop Through Columns And Sum Them At Bottom
I've been working on coding a summary sheet that pulls data from worksheets in the active workbook. I'm almost done, but am stumped at writing some code that will loop through the columns that I have copied and total them. I recorded a macro, but nothing happened when I ran it. What I have now is this code snippet that I found which works perfectly when I put my cursor in the active cell. I am not sure how I should write it into my sub. code that I found that works: Set rng1 = ActiveCell.End(xlDown) Set rng2 = rng1.End(xlUp)(2, 1) rng1(3, 1).Formula = _ "=Sum(" & Range(rng2, rng1).Address(False, False) & ")" I tried this: For Each col In NewSh ColNum = ColNum + 1 Range(RwNum, ColNum).Select Set rng1 = ActiveCell.End(xlDown) Set rng2 = rng1.End(xlUp)(2, 1) rng1(3, 1).Formula = _ "=Sum(" & Range(rng2, rng1).Address(False, False) & ")" Next col
View Replies!
View Related
Sum Of Columns When Criteria Is Met
1) If B1:2008 is "EA" (and so on for about 7 other classes) then count how many days are entered in G8:2008 so a cell on a seperate distribution sheet tells me how many sickness days in total have been taken by EA's (and then the same for the other classes).
View Replies!
View Related
Identify, List, And Sum 2 Columns
It's a time sheet that I need to have whatever data is listed in column E to be listed starting in cell C27 but only once even if it's listed several times. In cell D27 I need the sums to match the data to it's left with the values from above in column D. I've used fill colors to help show my intent. I would also like it to keep track of remaining vacation time by recognizing the word 'VAC' from column E and subtract the value from it's left. This value is shown in cell J26 as '46hrs REMAINING'.
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
Macro To Multiple Two Columns Then Sum?
I have a work book with 97 sheets, all of the sheets are identical in format but contain different data. I need to multiple column F (which is a price) by column G (which is a quantity). I then need to sum the results and spit this number out on a new sheet. So in then end I would have 97 entries on the new sheet with this new sum which would give me are total stock value on hand.
View Replies!
View Related
If Sum Of Columns Is Blank Show Another Value
I attach a file for you to more easily understand my problem. I have a big table with information, then I would like to summarize this, if all fields for certain "Type" (see file) is blank, then I would like to use another value that I keep as a separate row in the summarize table. But I also need to have it to work if zeros are put in, I need to show that value, that's my biggest problem right now when using sumif forumla.
View Replies!
View Related
Sum The First 12 Columns After The First Positive Number
I need a formula that will look at a row (say row 1) starting in column F. When the first positive value appears in the row, the formula will then sum that value and the next 11 columns in the same row. So for example, starting in row 1 column F  here is an example list of values 0 0 0 0 3 4 3 2 0 4 8 7 9 0 9 0 6 4 2 2 0 8 7 The total would be 49 (start summing at the first 3 (which would be column J)and finish with the 0 (which would be column U)) Lets assume the formula is in column AZ, row 1 and I then need to drag it down
View Replies!
View Related
Dont Sum Hidden Columns
I have a spreadsheet with rows as budget line item and columns for the months they occur. I have a cell on each row (in column B) displaying the total of that row for all the months. (columns C to Z) I want to be able to hide certain months (columns), and have the total not include those hidden columns; but the formula for the totaling cell ( "subtotal(109,C3:Z3)" ) appears to continue counting all the columsn, even the hidden ones
View Replies!
View Related
Multiplying Rows By Columns And Sum Them
I need to multiply the values of 10 rows by the values in 10 columns and sum then, akin to the sumproduct function, which only seems to work if the values are all in rows or all in columns. In other words; rows of 1 2 3.......10 & columns of a b c . . j Expressed as (1*a) + (2*b) + (3*c) + ....... (10*j) I can do it long hand and I can do it by transposing the data as an intermediate step, but I was looking to do it all in one formula.
View Replies!
View Related
Sum For Columns Containing Percentage & Date
I have a column in a spreadsheet that I enter a percentage complete, or a completion date in, beside it I have a column that shows "diameter inches" I am looking for a formula that will multiply the percentage in the first column by the inches in the second column, and give me a total at the bottom of the page. The problem is that when there is a date entered, I need it to be considered 100% and add the full amount of the corresponding cell to the total.
View Replies!
View Related
Count And Sum For Criteria In Multiple Columns
I am trying to figure out how to create a formula using multiple criteria in different columns. Ideally, I need to use the whole column (i.e. E:E rather than E2:E400) because I don't want to have to update the formula every time I input data. I will simplify my spreadsheet for example purpose. Basically, column A has a unique identifier that either begins with an "M" or an "R." Column B either contains a person's name or a "". Column C contains a dollar amount. 1. I need to be able to count all the cells in Column A that begin with an "M" AND have a "" in Column B. 2. I need to be able to SUM the $ amounts in Column C ONLY for the items that begin with an "M" in Column A and have a "" in Column B. Is there any sort of formula that might do this? I have tried SUM arrays but as I said before, I would rather be able to use the whole column.
View Replies!
View Related
Lookup/Match/Sum  Several Worksheets And Different Columns
I'm working on budgets (salaries). I have one main spreadsheet with all employee names listed  other spreadsheets are for each department. I need to bring the total salary from the department spreadsheets and would like to do this by looking up (or matching?) the names in the column from the main sheet to the department sheets and then putting the salary from the department spreadsheet to the main spreadsheet. In addition, I have several that have their salary split between two or more departments. On my department spreadsheet I have a column labeled % of salary for this department. On the main spreadsheet, I want to figure out a formula that will sum the percentages for a certain person (so I make sure I don't go over 100%). For example  Joe Smith's name is on the main employee spreadsheet. He is also listed on 3 other department spreadsheets, with % of salary listed as 30% for department 1, 30% for department 2, and 40% for department 3. On the main spreadsheet I need to add up Joe's percentages from the department spreadsheets.
View Replies!
View Related
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 Replies!
View Related
Sum Of Numbers But Dependant Upon Another Columns Entry
I want to show the number of weeks remaining from the sum of a column BUT I want it to distinguish between the entry in another column. See below. Columns B to L represent a number of weeks EG1 Column C: Year 1 has a set amount of 47 and C156 displays this. C157 should display the amount of weeks in Column C but only when a number "1" appears in Column "Y" C158 displays the total amount remaining EG2 Column H: Year 2 has a set amount of 38 and H160 displays this. H161 should display the amount of weeks in Column C but only when a number "2" appears in Column "Y" C162 displays the total amount remaining
View Replies!
View Related
Sum Function Fails: Automate The Columns
I have a spreadsheet that I am trying to automate, one of the tasks is to sum some columns namely columns O to T. Column B contains the exact number of entries in the columns O to T, therefore I thought that this would be a good counter. The Spreadsheet varies in number of entries every time. In this instance the rows are in total 200 inclusive a header row. ie r below is = to 200. I have tried to record a macro and from that I have tried to replace the pertinent values. Sub Sel_Sum () Dim r As Integer Dim s As Integer Dim t As Integer Dim U As Integer Range("B1", Range("B65536").End(xlUp)).Select r = Selection.Rows.Count s = (r  1) * 1 t = r + 1 U = 1 Range("O" & t, "T" & t).Select Selection.FormulaR1C1 = "=SUM(R" & [s] & "C:R" & [u] & "C)" 'fails here Selection.NumberFormat = "#,##0.00_ ;[Red]#,##0.00 " End Sub
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 Range: Multiple Criteria In Rows & Columns
I posted this on Mr Excel, but did not get an answer. I hope someone here can help. I have a rather large financial worksheet that I am trying to extract some specific data from. The sheet contains budget and expense #'s for the fiscal year for several projects. The project names are listed in column G and the same project can be on multiple lines, depending on what the expense is. Column J contains either "Cap" for Capital, or "Exp" for Expense. The monthly data starts at Row L. Each month has 6 columns, the one that matters for this scenereo is the 5th one for each month, which is the Forecast Column. Our Fiscal Year is April 1  March 31, but this sheet ruins from March  March so that we can carry over anything from the previous year. So the forecast column for March is column "P", April is column "V" and so on, adding 6 columns for each month....... With me so far? What I am trying to do is get the total forecasted expenses amount for a specific project for the remainder of the fiscal year. In other words I need to look for a specific Project Code in Row G, look for "Exp" in Row J, Then get only the numbers that are in the Forecasted Column for months in the future and add them all up. Just in case that is not totaly clear criteria to meet Column G = "Project Code" Column J = "Exp" Row 2 > this month Row 3 = Forecast.......................
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
Format Table & Add Sum To End Of 2 Columns
I have a code to apply formatting to my selected cells (a border and background color) as well as sum in rows in columns C and D. I have data in A1:D1508. The first row is titles. Column A has item codes, B is item titles, C is 2007 sales and D is 2006 sales. All the items are grouped by categories. For example A1:D7 is category 1 with row 8 containing a total in column C and D, A9:D14 is category 2 with totals in row 8. Not all the categories have the same number of rows. This code is giving me a Runtime error '1004': Applicationdefined or objectdefined error. I have searched the forums and can't find what is wrong with my code. Sub CustomFormating() With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlDouble .Weight = xlThick .ColorIndex = xlAutomatic End With...............................
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
Sum With Multiple Criteria A Number Of Rows And Columns From Original Data
I have 6 columns of data laid out as follows, columnA columnB columnC columnD columnE columnF XXXXXXXX XXXX XXXXXX XXXX XXXX XXXX XXXX XX XXXXXXXX XXXX XXXXXX XXXX XXXX XXXX XXXX XX I need to find data in columns B and C and add the figures from column F, which drop down one line. I have written the following formula which is giving me far higher figures than I should be getting = SUMIF($B$2614:$B$2640,"bhree*",IF($C$2614:$C$2640,">=01/07/2006",( OFFSET($C$2614:$C$2640,1,3,1,1))))
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. quarterwise sum/count of 1s
View Replies!
View Related
