#### Subtotals for a variable number of rows

```Following is my data:
A                            B     C
1 Oranges                 6
2 Oranges                 5
3 Oranges                 7
4 Pomegranates       19
5 Pomegranates       16

C3 should be 18, and C5 should be 35.
Next week there will more more or fewer categories with a variable number of
entities in each.

A macro to accomplish this will save me a lot of time (and errors ??).
--
“Doubt is uncomfortable, certainty is ridiculous.”  (Voltaire)
```
 0
Utf
4/7/2010 6:55:01 AM
excel.programming 6508 articles. 2 followers.

3 Replies
1579 Views

Similar Articles

[PageSpeed] 15

```Subtotal feature or pivottable based of dynamic named range;
http://www.ozgrid.com/Excel/subtotal.htm
http://www.ozgrid.com/Excel/excel-pivot-tables.htm
http://www.ozgrid.com/Excel/DynamicRanges.htm

--
Regards
Dave Hawley
www.ozgrid.com
"bud i" <ibud.no-spam@bellsouth.net> wrote in message
news:8E02A42E-60D6-4A1B-BD93-CDDCB163ED40@microsoft.com...
> Following is my data:
>  A                            B     C
> 1 Oranges                 6
> 2 Oranges                 5
> 3 Oranges                 7
> 4 Pomegranates       19
> 5 Pomegranates       16
>
> C3 should be 18, and C5 should be 35.
> Next week there will more more or fewer categories with a variable number
> of
> entities in each.
>
> A macro to accomplish this will save me a lot of time (and errors ??).
> --
> “Doubt is uncomfortable, certainty is ridiculous.”  (Voltaire)

```
 0
ozgrid
4/7/2010 7:42:52 AM
```Give this macro a try...

Sub SubTotals()
Dim X As Long, LastRow As Long, LastSubTotal As Long, Fruit As String
Const StartRow As Long = 1
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Fruit = Cells(StartRow, "A").Value
LastSubTotal = StartRow
For X = StartRow + 1 To LastRow + 1
If Cells(X, "A").Value <> Fruit Then
Cells(X - 1, "C").Value = WorksheetFunction.Sum(Range(Cells( _
LastSubTotal, "B"), Cells(X - 1, "B")))
Fruit = Cells(X, "A").Value
LastSubTotal = X
End If
Next
End Sub

--
Rick (MVP - Excel)

"bud i" <ibud.no-spam@bellsouth.net> wrote in message
news:8E02A42E-60D6-4A1B-BD93-CDDCB163ED40@microsoft.com...
> Following is my data:
>  A                            B     C
> 1 Oranges                 6
> 2 Oranges                 5
> 3 Oranges                 7
> 4 Pomegranates       19
> 5 Pomegranates       16
>
> C3 should be 18, and C5 should be 35.
> Next week there will more more or fewer categories with a variable number
> of
> entities in each.
>
> A macro to accomplish this will save me a lot of time (and errors ??).
> --
> “Doubt is uncomfortable, certainty is ridiculous.”  (Voltaire)

```
 0
Rick
4/7/2010 8:07:01 AM
```Hi Bud,

Is it really necessary to have the formula in column C adjacent to the last
record of the particular type? If not, you could set up a little table either
on the same worksheet on on another worksheet like the following with a list
of unique values of your column A.

Col E                 Col F
Item                  Total
Oranges            18
Pomegranates    35

You can then use SUMIF. See Help for more info on this.

Your formula in F2 in the above would be
=SUMIF(A:A,E2,B:B)

You only need to copy the formula down and you can add or delete items as
required.

If you decide to have your table in another worksheet then the formula would
be like the following. (Assuming the table is in columns E and F)

=SUMIF(Sheet1!A:A,E2,Sheet1!B:B)

--
Regards,

OssieMac

"bud i" wrote:

> Following is my data:
>   A                            B     C
> 1 Oranges                 6
> 2 Oranges                 5
> 3 Oranges                 7
> 4 Pomegranates       19
> 5 Pomegranates       16
>
> C3 should be 18, and C5 should be 35.
> Next week there will more more or fewer categories with a variable number of
> entities in each.
>
> A macro to accomplish this will save me a lot of time (and errors ??).
> --
> “Doubt is uncomfortable, certainty is ridiculous.”  (Voltaire)
```
 0
Utf
4/7/2010 8:36:01 AM

Similar Artilces:

Subtotals by page
Is there any way of getting excel to generate subtotals by page? these should update automatically when pagination changes. ...

Vc.Net member variables for dialog in MFC extension DLL not available ?
I have an application with MFC extension DLL's with dialog's both in the main app and in the DLL's. Apart from care in the resource Id specification it works fine using classwizard in Vc 6, allowing me to define member variables in the min app and the dll's However on converting to Vc.Net the Add Member Variable wizard is unavailable for the dialogs in the DLL's, but seems to work normally in the main app. Why is this so ? and is there a work-around ? Is this related to resource issues. All the code and variables generated by Vc 6 work fine,I just can't see or modify t...

Copy/Paste cells containing only numbers with .50 in it
I have been trying to come up with a formula that will read each cell in a column and copy & paste only the cells that contain numbers with .50. I can seem to properly configure the formula to read any whole number as long as it contains .50 in it. I want these vnumbers pasted into a seperate column. =IF(D21="*"+"0.50"),(D21),("") Thank you very much for your assistance. =3DIF(D21-INT(D21)=3D0.5,D21,"") If you truly want the numbers copied and pasted you would have to use = VBA. Gord Dibben MS Excel MVP On Wed, 2 Jun ...

extracting numbers from a string
Hello, I'm trying to extract numbers from a string stored in an Acces table field. What is the best method to do this or the correct function to use? Thanks. "fiaola" <fiaola@mail.com> wrote in message news:eXm39F4YIHA.5028@TK2MSFTNGP04.phx.gbl... > Hello, > > I'm trying to extract numbers from a string stored in an Acces table > field. What is the best method to do this or the correct function to use? That will depend on how the numbers are stored in the string, and to what extent the string format is consistent. What do your field values look ...

Reference number
After adding a sales note to a transaction with a reference number, how do I pull up a list of all the transactions with that reference number? I haven't seen any reports out there (custom reports in customersource) that are based on the transaction table....if you know how to use SO Administrator then a query will work...(replace XXX below with your reference number but keep the single quotes) select transactionnumber, time, referencenumber from [transaction] where referencenumber = 'XXX' order by time Hope this helps... "Michael" wrote: > After adding a sa...

How do you define variables in excel?
I am setting up a spreadsheet to keep track of my students. I want to use excel if possible to keep track of lates, left early, attendance, etc, as my grades are kept there already. I was wondering if you can assign values to variables to accomplish this and how to define them. Thanks Why do you want to assign to variables, why not just use worksheet cells? -- HTH RP (remove nothere from the email address if mailing direct) "BigRon" <BigRon@discussions.microsoft.com> wrote in message news:D246057F-E2D9-474F-85A2-A31440E48142@microsoft.com... > I am setting up a s...

To have autonumber start from a certain number
Hello, I am creating a database to replace our excel to record our church membership. Our last number is 580 on membership table, and on address table is 193. During the construction I did not realize that if we delete autonumber for the testing data, it will not continue with the next number of 581... There are 5 records testing that I deleted and when I see now it shows 586. I have finished testing, I want it to show the next number is 581. The same problem with the Address autonumber Please help what should I do. -- H. Frank Situmorang In some versions of Access, all you have to ...

Number to text (checkbook)
could anybody tell me how to set Excel to write out a number in English? I need a way for Excel to refer to a number in one cell/worksheet and turn it into a checkbook type nuber in another (e.g., "112.19" becomes "One Hundred Twelve and 19/100" or "One Hundred Twelve Dollars and 19 cents" ). Please Help! see your post in misc, please do not post to more than one group at a time -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated!...

Counting the number of dates?
In a worksheet of mine I have a bunch of dates in column A in the format dd/mm/yyyy. I also have data in between the dates in that column and I was wondering if there was a formula that will count the number of dates. Thanks Aaron If the other data is not numbers you can use =SUMPRODUCT(--ISNUMBER(A1:A100)) if there could be other numbers, then if you know the start and end dates you could use =SUMPRODUCT(--(A1:A100>=DATE(2005,1,1)),--(A1:A100<=DATE(2005,12,31))) -- HTH RP (remove nothere from the email address if mailing direct) "aaronwexler" <aaronwexler@disc...

Filter Subtotal
Is it possible to filter out a subtotal row? If I have a matrix as follows, is there a way to remove the Subtotal3 row/line? Total Labor 75.00 Overhead 50.00 Subtotal1 125.00 Training 50.00 Travel 50.00 Subtotal2 100.00 G&A 75.00 Subtotal3 75.00 <<---remove? Total All 300.00 Hopefully this came out right, sorry if it did not. Is there a way to attach an image? You can do the row visibiliy to false, You need to have an appropriate expression set he...

Filtering Rows in Excel?
Hi I have a colleague who wishes to filter across a row and not a column. As I understand it the filter functions on Excel are specific to columns. Any suggestions appreciated. Peter You could Transpose to Columns. If you have fewer than 256 rows to filter in this manner, you could: Copy the range to filter. Go to a new sheet. Edit | Paste Special | Transpose Then, you will have Columns instead of rows, and you can use the AutoFilter. tj "Peter W" wrote: > Hi > I have a colleague who wishes to filter across a row and not a column. > > As I understand it the fil...

extracting numbers from data
Hi, Is there a way to extract the numbers, remove them and place them in an adjacent column from the cells in the example below? The LEFT function does not do the entire trick, since the amount of numbers differs per cell and I still need to delete the number in the original cell. 1bla 1.1blabla 1.1.1blablabla 1.1.1.1blablablabla Thanks ...

matching a column of numbers to another in another spreadsheet
I have a spreadsheet of shortpayment amounts and their invoice numbers on one spreadsheet with a column of credit amounts with their invoice numbers on another spreadsheet. I need to match the credits written with the shortpayments. kinda like this spreadsheet 1 spreadsheet 2 inv # amount date Credit # Amount Date 122334 15.00 11-25-09 675555 15.00 12-10-09 223345 22.00 10-20-09 754444 22.00 111-25-09 naturally...

HLookup and Row Index Number
Hello. I am currently using the following formula, but want to improve it: =HLOOKUP(C3,'Drivers'!\$C\$4:\$CT\$34,5) Currently it is retrieving the number in the fifth row, but I am concerned that the number will change if the reference worksheet is ever altered. Is there a way to have Excel look up a value that is in a row with a certain label (eg, "Average Size") instead of the fifth row? Thanks! You can, using the MATCH() function on the row with the labels. Look in HELP for details. But of course it would have been better if Excel could do it the way you describe, at lea...

Adding + in front of a number
I have a column of numbers which represent a change in value over the previous year. I want to have a + in front of the number if it's a positive change. Let's say my first number is 500. My problem is that I can write something like, if(a1>0,"+"&a1), but when then I can't format the number. If the number is in the millions I can't provide a comma as a thousand separator and I cannot tell it how many decimal places I want to show. Is there a really easy way to do that, or do I have to write a huge formula to precisely spell out the formatting within...

bin number on sop report
Hi all we are using gp 9.0 and would like to add bin numbers to the sop blank invoice form and are having some problems does anyone know how this can be done ? Hi Steve, Due to a report writer limitation of only being able to have one 1 to many relationship from the SOP_LINE_WORK table, it would take a fair amount of work to add the bin. Currently the Serial/Lot information links to the SOP_LINE_WORK table and that is a 1 to many relationship. If it so happened you do not use serial/lots it would be easy because you could remove the serial/lot information and table that is used in ...

Subtotals?
On the Account List page I wish there were sub-totals under Favorite Accounts and Other Accounts. Anyone know of a setting or hack to do this? Future functionality? ...

Automatically UnHide rows as per requirement
How Can I Insert Rows as per requirement? I am working on an Expense Template. I have spared around 20 rows for the User but generally only 5-10 rows required. Now Is it possible that I start with 3 rows and if user needs more space then those hidden rows Unhide one by one as per the need arise. I will protect the workbook. You could give the users a macro that would: ask how many rows to unhide unprotect the worksheet unhide those rows reprotect the worksheet Kind of like: Option Explicit Sub testme() Dim wks As Worksheet Dim myHiddenRows As Range Dim HowManyToShow As Lo...

Excel Variable Save Name problem
I have written a program for work that is basically a daily cash u spreadsheet, however I would like to save each days report as a backup Ideally I would like to save the worksheet as that days date by us of a command button/macro to reduce the chance of user erro( ie savin under wrong name) The date is on the worksheet, but I can't figure ou a way to use a range as a save name. I would really appreciate some help with this problem. Cheer -- Message posted from http://www.ExcelForum.com Hi Lippa73 One way to save a copy in C:\ with a date/time stamp Sub test() Dim strdate As ...

Automatic Numbering Upwards
I'm trying to set out a worksheet for payments out for my company. The only thing that I'm having trouble with is the record number field. I could type the incremental number at the beginning of each record myself but I was wondering if it was possible to get Excel to do that automatically. If possible, I'd like to have it enter the next number up in the field below when a new record is added. Is this possible? TIA -- Alex01 ------------------------------------------------------------------------ Alex01's Profile: http://www.excelforum.com/member.php?action=getinfo&use...

Average various variable ranges
Hi, I posted this question before, but I cannot find the thread of it anywhere, so I am posting it again. If it is duplicated, please forgive me. I have 90 000 rows of data that is in the following format: CW001P01-SH-C: Time Duration Partition Utilization % 20/03/2010 23:56 900 86.3521441 21/03/2010 00:11 901 86.35425916 21/03/2010 00:26 899 86.35738494 21/03/2010 00:41 902 86.3596435 21/03/2010 00:56 901 86.36061494 21/03/2010 01:11 901 86.33145463 21/03/2010 01:26 912 86.33382161 CW001P12-SH-F: Time Duration Partition Utilization % 20/03/2010 23:56 900 2.55062256 21/03/...

Sequential number assignment
I need to assign a sequential number to a serial number. The serial number is the YEAR and a LETTER for the Month. Then followed by a sequential number for the that month and year. The sequential number starts over each month. The number must start with a 01. How could I automate this and insure I don't duplicate it? Thanks Matt -- Matt Campbell mattc (at) saunatec [dot] com Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200703/1 Hi Matt, Here is a function that will return an incrementing number which will start over on the 1st...

Pivot Table number format won't save
Hi, I have forms which display in PivotTable view. One column is a percentage of the row total. I have set the number format in the properties sheet to #0% to take the percentage back from two decimal places to none. This works fine while the form is still open. If I click save and close the form the next time I open it the formating is back to two decimal places. How do I retain the number formating, please? John -- John Whyte ...

Top row of excel stays even in sorting
I need a header in the top row of my excel worksheet so that even when I sort the data the top row remains the same. 2003:- Data>>Sort>>My Data Range As>> Check the “Header Row” Option Button. 2007:- Press Alt+D+S the Sort Dialog Box will appear Press Alt+H which will select the “My Data has headers” check box. -- Remember to Click Yes, if this post helps! -------------------- (Ms-Exl-Learner) -------------------- "willyd" wrote: > I need a header in the top row of my excel worksheet so that even when I sort > the data the top ro...

subtotal, match?
Morning all. I need to do a name check to match with a subtotal function. Is that possible? I was thinking that subtotal(Func_Num,match(....)) might handle it, but I don't see anything that'd allow for that in the list of function numbers for subtotal. My goal is to perform a subtotal type operation, to compare names in a field range from one worksheet to another. I'm already performing a subtotal operation on one field, and wanted to do an if test on the names that show up with each operation. With the false response, I'll then be changing the name eleme...