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 Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
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
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
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
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
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
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 Based On Multiple Conditions
I am trying to sum numbers based on three counditions. My problem the third condition needs to use a wild card. I have attached a sample file, in the last condition I am only wanting to sum numbers where the characters in the last column begins with bd* The yellow cell contains the formula I am using.
View Replies!
View Related
Sum Based Upon Cell Color  Multiple Times
I have a workbook with a tab called "parts list" where there are thousands of parts with prices. I need to sum all of the parts based upon their location within the list. The list is divided with the following headers  Yellow (Assemblies), Gray (SubAssemblies) there can be many under a yellow header, and Green (Components) there can be many under a Gray header. I have been summing the areas manually, and I am hoping for a more automated way of creating the totals by running a macro. I need to sum (Column K) from Yellow+1 to Yellow1 and deposit the sum in the starting Yellow row  Column L. This needs to be repeated for as many Yellow Headers as there are in the list. I then need to repeat the process for Gray Headers  Col K, Gray+1 to Gray1, and put total in starting Gray header, Column L. Once again, there can be many Gray Headers under a Yellow header, and this needs t be repeated throughout the list. I then need to repeat this for the Green Headers  same as above Gray. I am hoping that this can be done by color, but if it's easier, I can sort the list by color and in column M, add an "A" to all Yellow Headers, "S" to all Gray Headers, "C" to all Green Headers, and "E" to represent the end of the component list 
View Replies!
View Related
MultiCriteria Sum Based On Multiple Column Cells
I have a large worksheet that lists employees and the hours they work each day in a pay period. For Example: Column A holds a date and Column E holds the number of hours worked for a paytype. So i want to create some function that sums the numbers in column E, but only those where column A holds a date. Then i want to return that number to a cell, say C20. I think I've managed to tell the macro which rows i want to return values from, but I can't figure out how to say "add these numbers together and put the total in C20." I'll check this frequently if clarification is needed.
View Replies!
View Related
Sum Based On Multiple Conditions & Variable Range
I have a rolling calendar worksheet that I need to create a formula to auto populate the sum of 8 weeks out, 4 and 8 weeks back based on any work week date I may plug in. So on the "summary page" based on a work week date, I need 8 weeks out, 4 and 8 weeks back projections to be auto summed for 10 different part numbers. The "13 week rolling" page will continue to be updated so that everyweek another week is added and the last week will fall off. (this part can be done manually if no function can delete the oldest column) When the weeks are added it must include the workweek in question. i.e. ww25 = (8weeks out) ww25:ww32 / (4weeks back) ww25:ww22 / (8weeks back) ww25:ww18. see example workbook attached. This has been driving me nuts for a few weeks now.
View Replies!
View Related
Transpose Multiple Columns To Rows Based On Criteria
I am stumped on how to transpose multiple columns to rows based on specific criteria. Here is an example of the data I am working with: Acct #Rev CodeUnitsCharges10094537034503$0.0010094537034501$605.0010094537037101$0.0010096359034503$0.0010096359034501$355.0010096359037101$0.00 I want it to look like the following: Acct #Rev CodeUnitsChargesRev CodeUnitsChargesRev CodeUnitsCharges10094537034503$0.004501$605.007101$0.0010096359034503$0.004501$355.007101$0.00 I should note that there is oftentimes more than three rows for the same account number, sometimes it could be as many as 20 rows for the same account.
View Replies!
View Related
Transpose Multiple Columns Into Rows Based On Repeats
I need to transpose a three column worksheet with thousands of rows containing repeats based on the value in Column A (between 2 and 11 consecutive repeats), into rows with no repeats, and the values from the repeated rows into new columns. Column A has a unique numeric value corresponding to the repeated rows. Column B has 1 of 11 values and Column C has 1 of 4 values. The worksheet looks like this: 1 abc x 1 def y 2 ghi x 2 abc n 2 lmn x 2 def z 2 jkl y I need to make it look like this: 1 abc x def y 2 ghi x abc n lmn x def z jkl y I tried using the following code, but it dropped all the values from column C: Sub kTest() Dim a, i As Long, w(), k(), n As Long Dim dic As Object, ws As Worksheet, s As String Set dic = CreateObject("scripting.dictionary") dic.comparemode = vbTextCompare With Sheets("sheet1") a = . Range("a2:b" & .Range("a" & Rows.Count).End(xlUp).Row) End With I am attaching a workbook " Book 1" that has the results from the above macro in the first worksheet "Final Report", the origninal data "orig data", and the format I need to get the data into "needed data".
View Replies!
View Related
Unhide/hide Multiple Columns Based On Date
I need to show hidden columns based on the date I entered. For example, if I entered "1/1/1990" on a1 as the starting date and "4/30/1990" on b1 as the ending date. I want Excel to show the columns that are covered by the date, thus it shows Jan, Feb, March and April. How do I do that? Here's an example attachment. In here Sheet 1 is the starting point, the highlighted cells is where I enter the date. the Result sheet shows what I want Excel to show me when I have a date entered.
View Replies!
View Related
Summing Values From Multiple Worksheets Based On Rows And Columns
I have Master sheet where I collect info from sub sheets. All sheets are similarly formatted, ie. product numbers on column A and headers on row 2. I need to sum values from all sheets based product number and header. Master sheet includes all product numbers and some extra headers, sub sheets include only needed numbers. Headers on sub sheets are identical. Currently I have this thing solved with following formula: N48=sumproduct(sumif(indirect("'"&$B$378:$B$385&"'!A:A");A48;indirect("'"&$B$378:$B$385&"'!L:L"))) Where B378:B385 includes sheet names. But problem with this is that column is hard coded, so I have to know that that value I am looking for is in column L. That wouldn't be show stopping problem on its own, but I have columns all the way to DR and copying formulas for each column takes a lot of time when I have to manually update each column. Just copying cell holds that L:L and doesn't change it. So, in addition of getting values for specific product number I need to get values from specific column based on column header.
View Replies!
View Related
Return Multiple Values Across Columns And Rows Based On Criteria
I have a table with column headings of product ID Numbers (eg.1111) and row headings of Store number (Eg.1) with data showing the time each product was last sold at that store, I need something to consolidate for each store which Product ID's were sold prior to 5pm and what time they were sold. EG Store 1 1111 16:40 2222 13:00 Store 2 1111 15:05 3333 16:50
View Replies!
View Related
Hide Rows Based On Multiple Columns Formula Returning Empty Text ""
I would like to rows based on multiple column conditions criteria. ie., if the columns N, O, P values are "", then hide the particular row. The logic given in the website here, i tried But, it is not 100% working. It works for a few rows at the start of the database & it works for the rows at the end of the database. In between, for a few rows, even if the column values are "" it does not hide those rows.
View Replies!
View Related
Copy Multiple Columns Groups To 1 Group Of X Columns
Example:................ The key point is that I have over 400 columns grouped in three, offset by one row down for each (this is due to a different formula I have working). Each column is 60 numbers long (not including empty cells above data). I want to copy all of these columns into a single set of three columns on another sheet (Sheet2). (These will be the fifth, sixth, and seventh columns, E, F, and G. If it would be simpler, I could simply copy the third of each set of columns on Sheet1 (C, F, I) to column G of Sheet2. Then there would be a total of 144 columns being copied (60 numbers per column).) All of this copying has to be valuesonly as the original columns consist of formulas referencing outside cells. Sheet2 should look like this:.............
View Replies!
View Related
Move Data From 2 Columns Into Multiple Columns At Each Change
I am trying to write a macro that will take data from column A and use it as a column header in column C, and then take the corresponding data in column B and list it under the column header in column C. When the value in column A changes it will move on to column D and then list the corresponding data from column B underneath it in column D until all of the data in the 2 columns is spread across the worksheet. Example:..............
View Replies!
View Related
Sort Multiple Columns, With Blanks, And Move All Columns Into One
I found this code on Ozgrid to sort all columns of a worksheet that were continuous with no gaps or spaces that works well: Sub CopyToA() Do While ActiveCell <> "" Range(ActiveCell, ActiveCell.End(xlDown)).Cut Destination:=Range("a65535").End(xlUp).Offset(1, 0) ActiveCell.Offset(0, 1).Select Loop End Sub However, I've tried to manipulate the code myself to 1) find all columns that aren't empty then 2) sort each column individually (WITHOUT expanding the sort to other columns) and 3) combining all the numbers into one seperate column. There are many posts concerning sorting but not one that addressed this particular situation.
View Replies!
View Related
Enter And Highlight Data In Columns Based On Other Columns
I have a worksheet with 20+ columns. For this macro, I only need to focus on 4 of them. However, none of these columns are ever in a fixed position so the macro would need to find them by name and NOT by column position. Here they are... 1. Vacation Type (will only have a text value of either "Cold" or "Warm") 2. Vacation Started (will always have a date *x/xx/xxxx) 3. Vacation Ended (sometimes it will have a date '*x/xx/xxxx' and sometimes it will NOT have a date and will be truly blank) 4. Number of Days (currently has ALL truly blank cells) THIS WHOLE MACRO SHOULD NOT BE CASE SENSITIVE ANYWHERE Here's what I would like the macro to do... Scenario 1  for "Cold" values Find "Cold" text values in the "Vacation Type" column "Cold" values WITH a date in the "Vacation Ended" columnIF there IS a date in the "Vacation Ended" column in the same row, put the number of days difference between the "Vacation Started" column and "Vacation Ended" column in the "Number of Days" column. The amount of days in the "Number of Days" column will determine whether these cells should be highlighted GREY or RED. Scenario 1AIF the number of days difference is 7 days or less, highlight the cells in the "Vacation Ended" column and "Number of Days" column RED. OR… Scenario 1BIF the number of days difference is MORE than 7 days, highlight the cells in the "Vacation Ended" column and "Number of Days" column GREY..................................
View Replies!
View Related
Copy Multiple Columns From Multiple Excel Files & Paste Into 1 Workbook
I have 8 different files all have a set of data in them each one has a long list of (column an) however the number of rows change by date. I need each file copied into the finalfile.xls one after another. in the files that will be merged into the final file the final row i need copied is blank. I have all the copy formulas and everything set, I just need a range to copy that automaticaly takes cell a10 to the first blank a cell from each file and pastes it in the finalfile.xls under the last paste so they dont over write each other.
View Replies!
View Related
Copy Rows Meeting Multiple Criteria On Multiple Columns
1. Copy data from original file (I do not want to do anything in the original file) into the spreadsheet (Target worksheet)where the code should run. 2. In sheet 1 of Target Worksheet, there are 2 columns which I need to set criterias on namely Column D and Column L 3. In Column D, I want to specify 3 criterias namely A, B and C 4. In Column L, I want to specify 5 criterias namely London, Frankfurt, New York, Sydney and Tokyo 5. If Criterias in 3 and 4 are met, copy all rows into Sheet 2 of Target Worksheet
View Replies!
View Related
Sum Across Multiple Tabs, Multiple Criteria
Excel 2007 My workbook contains 13 tabs  1,2,3,...12, and Summary My data starts on line 4 of every sheet but varies in length  so far the longest goes to line 30. Rows used on all 13 sheet are as follows: A  contains facility names B  contains a two or three letter code C  contains hours D  contains dollars E  contains adjusted rate On the Summary tab I have listed all the facilites and two or three letter codes. I need to sum column "C" on tabs 112 when they match columns A & B on the summary tab. I have tried the following but can't get them to work: =IF($A5=""," ",SUMPRODUCT(('1:[12]12'!A$4:$A$50=$A5),('1:[12]12'!B$4:$B$50=$B5),'1:12'!D$4:$D$50)) I did not put the [12] excel added that automatically I had 1:12 =SUMPRODUCT((THREED('1:12'!$A$4:$A$50)=A10)*(THREED('1:12'!$B$4:$B$50)=B10),(THREED('1:12'!C4:C50))) I just seen the THREED for the first time today and am not sure if this was the correct place to try but it didn't work anyway
View Replies!
View Related
Sum Multiple Product For Multiple Values
is it possible to use the sum product formula to give me the total results for multiple values? I know I can ask it to look at column a for a value of e.g "44" and column B a value of "gin2334" but can I ask it to ge me total for values from column A that equal "44, 45, 46, 47" ans B "gin2334"? if not has any got a clue as to how i would get these results?
View Replies!
View Related
Multiple Sum Formula With Multiple Constraints
I have listed data below for purposes of the formula: HTML CustomerInvoice Invoice Amount (Rep. Invoice Month) Nike1 1,000 Reebok1 600 Adidas1 700 Puma1 600 Nike1 (250) Reebok2 1,000 Adidas2 600 Puma2 700 How do I get the data above, into a spreadsheet like the one below? I have tried If/Than Statements with no luck. I need to total the amount of invoices during a specific month for a specific customer.
View Replies!
View Related
Sum On Multiple Critera Across Multiple Sheets
I’m relatively new at using the INDIRECT function, and am having a hard time setting up the syntax for ranges, and even knowing if those ranges will work. I have a workbook with multiple sheets (let’s call them Program sheets) created from a template that contains variable numeric data that I need to sum by creating a formula on a Summary sheet within the same workbook. The criteria for IDing and summing the data from the Program sheets is spread over 3 cells in adjacent columns (let’s call them $E7, $F7 and $J7) on the Program sheets. A string concatenation of these cells will not create a unique string value on any one sheet as there are potentially multiple rows of data on each sheet and across sheets that could have the same value string. The Summary sheet is a report that contains hardcoded values in adjacent cells ($C4, $D4 and $E4) that will match values found in columns E, F and J from the Program sheets. I’d like to have the formula sum all values within the range P7:AA70 across all the Program sheets when the entries into E, F and J cells (from Program sheets) match $C4, $D4, and $E4 cells on the Summary sheet, keeping in mind that there could be multiple instances of the same values over several rows within the Program sheets (that’s OK, because I want each instance to be part of the sum) Here’s a formula I created for summing values found in a range based on a single matching criteria across sheets. Can this be adapted to the new sum formula I need? =SUMPRODUCT(SUMIF(INDIRECT("'"&Sheet_List&"'!G7:G70"),'ByMonth Summary'!$G7,INDIRECT("'"&Sheet_List&"'!P7:P70"))) Sheet_List is a named range on a separate tab that lists the names of the Program sheets that I need to sum from. P7:P70 is the range that the sumable data lies in. G7:G70 is the range that contains values that need to match the criteria on the ByMonth Summary sheet cell G7. For the new formula, I no longer want to sum based on criteria in the G column, but rather on criteria in the multiple columns I outlined in my diatribe above.
View Replies!
View Related
Change Multiple Columns Into Multiple Rows
I have a large spreadsheet that contains and employee number and then up to 5 entries of Dependants (each has its own column). What I need to do is have 1 for each of the dependant information instead of 5 and have multiple rows for the employee if they have more than one dependant. At the moment I am sorting the data by dep 1 name, pasting the data into a new sheet, deleting all the dep 1 stuff, sorting by dep name 2 and pasting that.........etc. It doesn’t take too long to do but I just wondered if there might be a quicker way as this will be something I will need to do more often.
View Replies!
View Related
Consolidate Multiple Columns In Multiple Worksheets
I am trying to consolidate information from multiple (atleast two or more) worksheets (in the same workbook) into a single sheet. The worksheets have identical column headings. There are 4 columns in each worksheet. The first three columns are text strings (and some cell in these columns may be blank). The fourth column is an integer. The number of rows in each worksheet is most likely to vary. Within a worksheet the rows are unique. However, some rows may be duplicated across worksheets. To reiterate, here's what I am trying to do. Step 1. Create a summary sheet. Step 2. When there is a unique row (identified by the first three cells in that row) across worksheets then I have that row as is in my summary sheet. Step 3. When there are duplicate rows (identified by the first three cells in that row) across worksheets, I want to add up the cell in the fourth column associated with those row in my summary sheet I have tried union (works only within a sheet), consolidate (works only with a single column). I don't need the sophistication of a pivot table but simply an aggregation of data I am trying to do this using macros (within an addin) I have enclosed a sample workbook with the expected output. I created this workbook by hand in an attempt to clarify my problem statement.
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
{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
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
