Calculate Totals Within A Range Object
Feb 21, 2007
I have a range object called tbl which consists of a number of rows and columns of numbers.
I have another range object called e that is the total row just beneath tbl. I want to sum up the total of each column within tbl and put the result in the appropriate cell within e.
I can do it using a for/next loop as shown below but there could be 10,000 rows and a hundred columns in my range which woulod probably take forever. I know there must be another simple way to do it but despite racking my brains and trying various things I can't get it. I'll be very grateful for any assistance.
My current solution is as follows. (e is selected).
'Calculate totals and display them
For g = 1 To tbl.Columns.Count ' the number of columns
h = 0 ' Holds the column total
For f = 1 To tbl.Rows.Count - 1 ' The number of rows
h = h + tbl.Rows([f]).Cells(g).Value 'add the cell value
Next
e.Cells(g).Value = h ' Display the column total
Next
View 8 Replies
ADVERTISEMENT
Oct 12, 2009
I am trying to calculate a total of a supplier between 2 dates. The total must fall in between the entered dates but the dates can be any given date and not limited to the dates in the supplied list. See attached scaled down example?
View 2 Replies
View Related
Apr 16, 2007
I want to compare cells and use a range of values to calculate totals (i.e. count if cell A1=boy and cell A2 equals either "fat" or "skinny")? Also, I tried using wildcard character "*" in the SUMPRODUCT formula, which didn't work (i.e. if cell A1="boy*" would return at TRUE if the cell contained "boy", "boys", "boy and girl", etc.). Is there a way to include wild card characters in a SUMPRODUCT formula like the ones described above?
View 9 Replies
View Related
Dec 12, 2007
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 1-100 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 9 Replies
View Related
Aug 7, 2014
I have a list of stores and the costs for various items.
Each store has (usually) more than one row.
Each store's items are totaled at the end of each line. Simple
What I do not know how to do is total all rows for each store, automatically. Like I said, I don't know how to explain it, so searching for it does not work out well for me.
Please see the attached sheet : How to Calculate Store Total.xlsx
View 9 Replies
View Related
Nov 8, 2006
I need to know how to calculate totals from a spreadsheet in conjunction with an IF statement.
I have a columns A & B below, i would like to calculate the numbers in coulmn B only if they match a specific criteria in column A.
For example: I want calculate the total of the numbers in coulmn 'B' only if column A is a '1'.
AB
24
33
12
21
35
13
22
View 10 Replies
View Related
Aug 21, 2013
I have a large spreadsheet, almost 9000 rows. I need to calculate the totals in column AB. There is a blank row seperating each group I need to total. Is there a faster and easier way for this to be done without adding the formula manually to each row? Here is a small example - I need to total the data under Total_USG
TOTAL_USG
LETTER_SMPLX
LETTER_DPLX
LEGAL_SMPLX
LEGAL_DPLX
154
162
[code]....
View 2 Replies
View Related
Jun 3, 2009
I have to calculate the totals for every individual for the past three months. I have to do this for 200 people, is there a formula i could use to do this? Eg. if a person made 50 sales in Jan, 40 sales in Feb and 100 sales in March i need a formula that calculates the grand total, which will be 190.
View 3 Replies
View Related
Mar 18, 2007
I need to calculate a subtotal like value on the last row of a filtered region.
e.g.
description year sum
name1 3 yrs ago 200
name1 2 yrs ago 230
name1 last year 250
name1 forecast 220
name1 forecast value 1400
name2 3 yrs ago 200
name2 2 yrs ago 230
name2 last year 250
name2 forecast 220
name2 forecast value 1400
How to put a value on the last row of filtered column. let's say that above list is very little in comparison with the real list. so, let's say that i look up for name36, witch begins on row (36*5) -> 5 is the values of the year column; after autofilter with "name1" the very left column with row index become blue and show rows from 1 to 5, than, next row is the last non-empty row.
View 9 Replies
View Related
Feb 25, 2014
I have a time sheet that I can figure the daily OT and DT on, but how to calc the the Bi-Weekly totals.
I can only have 80 hours per week, the rest needs to be carried to the OT field, but I can only have 40 of OT per week and the rest needs to be carried to the DT.
View 1 Replies
View Related
Mar 4, 2012
I have a spreadsheet called PRODUCTION, where information is entered daily. I need weekly totals (mon - sun) to be extracted into another worksheet.
View 7 Replies
View Related
May 28, 2014
Per the attached, I am looking to add restrictions to my formula based on YEARS OF SERVICE per the age restrictions each person falls into, those with 0-4 YOS who are under 20 years old etc.
Book10.xlsx‎
View 4 Replies
View Related
Jan 28, 2010
I have a spreadsheet that is now a yeare old with 5000 rows and is now going into the 2nd year
Column A is for date input and the same date can be repeated several tumes :-
1 Jan 09
1 Jan 09
1 Jan 09
1 Jan 09
2 Jan 09
2 Jan 09
3 Jan 09
3 Jan 09
3 Jan 09
Sometimes there are all 30 /31 days but normally not .
I need to find the last ocurance of the last date used for each month and then use the cell number to calculate the column totals for that month.
View 9 Replies
View Related
Sep 25, 2006
I am trying to figure out how to calculate how many permits were pulled by a specific company during certain dates (qtr). My column headers are (a)Date, (b) Builder. My dates are displayed as "Jan-06" but are entered as "1/1/2006".
How can I show how many permits were pulled by a specific builder during a three month period?
SUMIF, COUNTIF or SUMPRODUCT?
View 5 Replies
View Related
Mar 21, 2009
i need a macro that will copy pfolio and Totals 0 to sheet2 range A12, (i dont require the headers to be copied), the totals column is not always in Column B it varies, so the macro needs to factor this in. When copying the totals over i need the code to round the totals to 2dp, if there is a 0.00 in sheet 2 then clear cell and move one cell up.
Sheet1 AB9pfolioTotal10KFGEEE3411SDFSFF012ERWER1513REGERG0 Excel tables to the web >> Excel Jeanie HTML 4
Sheet2 AB11pfolioTotal12KFGEEE3413ERWER15 Excel tables to the web >> Excel Jeanie HTML 4
View 9 Replies
View Related
Jan 6, 2010
I'm trying to select a range that will be changing by column. I'm not sure why my syntax isn't working. What I've got:
View 2 Replies
View Related
Feb 25, 2010
I have a spreadsheet that tracks “points” for hourly associates on a daily basis that totals weekly and monthly. This spreadsheet works on a rolling calendar year instead of a fiscal and I need to be able to capture the totals for periods of time by days.
For example, I would need to view how many points ‘employee x’ has from 2/25/09 through 2/25/10. I have attached the spreadsheet, which includes tabs for each week ending and a summary page.
View 4 Replies
View Related
Feb 15, 2013
Have the following code:
Code:
Dim Recurring_Total as Range
With .Range("A" & Rows.Count).End(xlUp).Offset(,2)
Set Recurring_Total = .Range("A" & Rows.Count).End(xlUp).Offset(,2)
.Font.Bold = True
End With
What do I replace part in red with?
View 9 Replies
View Related
Aug 19, 2009
I'm doing somthing basically wrong here but i'm not sure what, everything seems to work as planned except in each case where a value is assigned the value isn't actually assigned. any ideas?
The basic objective is to convert a list of numbers (1 to 3 digits) to the format "000-" i.e. all three digits with a hyphen at the end.
View 9 Replies
View Related
Feb 24, 2013
How to include the workbook name in this piece of code?
Code:
sh1.Range(sh1.Cells(ii, k + 10)).Address
View 1 Replies
View Related
Jan 24, 2009
I have a while loop in which I'm updated information in 2 separate worksheets. I'm using the With-block statements separately to update each, but after the 30th iteration (and it's always on the 30th), the VBA code halts and get the "method 'value' of object 'range' failed" error message pointing to a line with the code as follows:
With Sheet1
.Cells(lngRow, 9).Value = intMonths
End With
where "intMonths" is an integer variable which I'm populated properly, and "lngRow" a long variable. When I debug both variables have proper data in them, and I have no idea why this is bombing.
View 9 Replies
View Related
Apr 2, 2005
When I try to pull some records from Oracle into Excel using ADO, I recieved this error msg:
Run-time error '-2147467259 (80004005)':
Method 'CopyFromRecordset' of object ' Range' failed
And here's the code I'm using:
Dim c As ADODB.Connection
Dim r As ADODB.Recordset
Dim sq As String
Set c = New ADODB.Connection
It's the standard ADO example used in this forum many times. Obviously there is some type of issue when the records are returned. I know that it isn't an issue with the number of records returned. It can return 30,000 fine but a few hundred may return this error. It's kinda random.
View 9 Replies
View Related
Mar 30, 2007
Sub test()
Dim rng, dest, c As Range
Dim result As String
Worksheets("sheet1").Activate
Set rng = Range([a1], [a1].End(xlDown))
Set dest = Range("d1")
result = ""
For Each c In rng
result = result & " " & c
Next
MsgBox result
dest = result
End Sub
the penultimate code statement "dest=result" does notwork the value of "result" is not entered in "dest" though no error comes up if I use "dest.value=result" it gives error "object required" If I use "range("d1")=result" it works.what is the theoretical mistake.
View 2 Replies
View Related
Feb 27, 2008
is it possible to write Range("A1:H6") without the string inside the range brackets? i'm trying to do is pass numerical column values into the Range function, and i'd rather not make a mess using the CHR function (CHR(65) = "A", for example).
View 2 Replies
View Related
Dec 3, 2008
I have a simple three column range. I Autofilter the range based on one of the values in Column 1. I then want to grab the results into a range object.
I've been trying to use the Specialcells(xlcelltypevisible) route to no avail. It only gets one row when I should have many.
View 14 Replies
View Related
Jun 23, 2009
I can't seem to figure out why this keeps giving me that error...
I've checked the row and column values inside and they seem to be ok. blank_ee() is an array of strings.
View 7 Replies
View Related
Nov 7, 2012
I've got a spreadsheet where I run a macro to Advanced Filter results from a "Register" sheet to a "Search" sheet. Macro is run from the Search sheet. Existing code looks like:
Code:
Sheets("Register").Range("A9:Z10000").AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=Sheets("Register").Range("A3:W4"), _
CopyToRange:=Sheets("Search").Range("A26:X26"), _
Unique:=False
At the moment the macro filters the range down to row 10,000. I need to adapt it to a variable number of rows in case they go over 10,000. Currently I am using a Do loop to find the bottom of the list by having it check the one column out of 26 that will always have data in it (Column D)
Code:
rfRegisterStarts = 10
rfRegisterRowCounter = 0
Do While IsEmpty(Sheets("Register").Cells(rfRegisterStarts + rfRegisterRowCounter, 4)) = False
rfRegisterRowCounter = rfRegisterRowCounter + 1
Loop
Therefore the range of my table to be filtered including header row is:
Range(Cells(rfRegisterStarts - 1, 1), Cells(rfRegisterStarts + rfRegisterRowCounter, 27))
My problem is that trying to change the Range object in front of .AdvancedFilter causes all sorts of errors. I've tried:
Code:
Sheets("Register").Range(Cells(rfRegisterStarts - 1, 1), Cells(rfRegisterStarts + rfRegisterRowCounter, 27))
.AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=Sheets("Register").Range("A3:W4"), _
CopyToRange:=Sheets("Search").Range("A26:X26"), _
Unique:=False
OR
Code:
Dim rfRegSelect As Range
Set rfRegSelect = Range(Cells(rfRegisterStarts - 1, 1), Cells(rfRegisterStarts + rfRegisterRowCounter, 27))
rfRegSelect.AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=Sheets("Register").Range("A3:W4"), _
CopyToRange:=Sheets("Search").Range("A26:X26"), _
Unique:=False
With almost every combination of
With Worksheet("Register") or Sheets("Register") I can imagine
Why I'm getting these errors? Is Advanced Filter just really finicky?
View 3 Replies
View Related
Jan 30, 2013
It is designed to submit data specific cells from a userform. It works fine when I select and day, 1,2,3, etc. until I get to day 27-31. For some reason I get a Range of Object Failed error 1004 every time. I don't understand what's changing to cause the issue. The red text near the bottom is the one that it tells me is the problem.
Code:
Private Sub Submit_Click()
Dim ws As Worksheet
Set ws = activesheet
[Code]....
View 9 Replies
View Related
Mar 28, 2008
how do we do something like Range("A1:B2") using an index for the cells. For example Range(R[0]C[0]:R[i]C[j]) which doesn't work. I know about Cells(i,j), but that only returns a range object of a single cell.
View 9 Replies
View Related
Apr 1, 2009
Here is my code
Dim LastCell As Range
Set LastCell = Cells(EndRow, LastCol).Address
I get a "Object Required" when this I try to set the address. I know this is easy.
View 9 Replies
View Related