#### 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. 0 followers.

3 Replies
931 Views

Similar Articles

[PageSpeed] 10

```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:

How can I Strike through a number in tables
I am creating a score sheet with columns of numbers. Some numbers need to be crossed out as a failed attempt. In microsoft word there is a command called strike through. Is there a similar command in publisher? If not is it possible to download from word that command You can retain the strikethrough from Word by pasting special, Microsoft Office Word document object. In Publisher you will have to manually use the line tool as Publisher does not have a strikethrough command. -- Mary Sauer MS MVP http://office.microsoft.com/ http://www.msauer.mvps.org/ news://msnews.microsoft.com "D...

Set value of const member variable in Init() function?
Is there a method for setting the value of a const member variable in an Init() function when the value is unknown in the constructor? TIA, Harvey "Harvey" <harveyab@juno.com> wrote in message news:1172562585.493563.230590@h3g2000cwc.googlegroups.com... > Is there a method for setting the value of a const member variable in > an Init() function when the value is unknown in the constructor? I don't believe so. But be careful of the difference between const int x=1; and const char *pszString; pszString = ... The latter is ok as the pointer is not const - th...

Offset, sum down to the first blank row
can anyone write the formula to sum down to the first blank row it encounters? In the sample data below I want the xx to sum only to 60 Assume the word sum is in cell A1 and the xx is in cell B1. sum xx Blue 10 Black 20 Red 30 Red 20 black 40 thanks much. p.s. would this formula be considered "volatile" and therefore "risky"....if so, what's the risk. thx. "Tami" <Tami@discussions.microsoft.com> wrote in message news:EFA4F8A3-A823-4522-A946-F55AB55E0B29@microsoft.com... > can anyone write the formula to ...

How to hide rows on condition
Hello all, Would anyone be able to provide me with some guidance on how I woul hide or unhide rows on a given condition? e.g. Say if cell A1 had value "HIDE" and cell A2 had value "C" then hide ro "C"? Thanks in advance, -E -- Message posted from http://www.ExcelForum.com Hi what is row 'C'??? -- Regards Frank Kabel Frankfurt, Germany "exceluser >" <<exceluser.16xfqd@excelforum-nospam.com> schrieb im Newsbeitrag news:exceluser.16xfqd@excelforum-nospam.com... > Hello all, > > Would anyone be able to provide me with...

Project resource hours subtotals
We have a program of linked projects that use an enterprise resource pool (Project and Server 2003). In Project's Resource Usage view, hours for all project work (not only these linked projects but any project that the resource has been assigned to) is totalled by resource. The resource shows 20,000 total hours but only has 50 hours of work on this particular plan. We are looking for a way to easily segregate these hours by project. We are currently accomplishing this by adding a Project column and then deleting any line item not associated with the project in question. This...

Page numbering #7
How can I, for example, leave the first two pages of my Publisher file unnumbered, have the next four pages numbered with Roman numbers (starting with i) and have the remaining pages numbered with Arabic numbers (again, starting with 1)? I'm stymied! Thanks for any help you can give. -Lyndie Assuming you are using Pub 2002 or 2003...........Go to your Page 1 (not the Roman numeral pages) Insert|Section -- JoAnn Paules MVP Microsoft [Publisher] "LyndieBee@yahoo.com" <anonymous@discussions.microsoft.com> wrote in message news:c40101c43856\$8c20cfc0\$a301280a@phx...

force Excel to treat numbers as text
Sorry I searched this and no one seems to have a solution. I trie pre-defiming columns as text but it gets over-riden by the paste. Past Special doesn't do the trick.. paste special as text on tabular data coming from a web site puts mos of the data in the first column. It no longer spreads the columns o pasted data into its own excel columns the tabular data, tab delimited, I want to 'copy&paste' looks lik this: 1) OGRODOWICZ MARK M47 6306 BROOKLYN NY 533 446 41 24:00 23:25 7:3 20:58 2) CHAO DUSTIN M35 3738 NEW YORK NY 534 447 155 24:02 23:47 7:40 23:1 The paste should...

roundup to next even number
I use this formula =SUM(A2*B2)/144 and need to roundup - but to the next even number so if calc returns 21.75 sb 22.00 or if 22.10 sb 24.00 I am brand new to this - any help would be appreciate -- Message posted from http://www.ExcelForum.com Hi one way: =ROUNDUP(A1/2,0)*2 or use =CEILING(A1,2) -- Regards Frank Kabel Frankfurt, Germany > I use this formula > =SUM(A2*B2)/144 > and need to roundup - but to the next even number > so if calc returns 21.75 sb 22.00 or if 22.10 sb 24.00 > I am brand new to this - any help would be appreciated > > > --- > Message...

Subtotal #4
Hi, I have a list of records as follows Apple orange grape apple apple grape watermelon I want to count the number of unique item above...ie, the end result should show 4 (apple,orange,grape,watermelon). Is there a formula to do this? Thanks! Val Hi Val, I suggest you look here: http://www.cpearson.com/excel/duplicat.htm#CountingUnique for a solution by Chip Pearson tim dolphinv4 wrote: > Hi, > > I have a list of records as follows > > Apple > orange > grape > apple > apple > grape > watermelon > > I want to count the number of unique item ...

Pub 2007 and Numbered Lists
Hi, I've just been trying out the Beta of Office 2007 and Publisher in particular. There have been some improvements; the PDF integration being the biggest. However it looks like the developers have worked on fluffy stuff like the templates rather than useful functionality (PDF being the exception). Here's an example of what I mean. I'm creating a question and answer list. Each question uses a text style that asks Publisher to apply a numbered list. The answer text (non numbered) then follows it. I then move on to the next numbered question and so on. Publisher refuses to continu...

Hindi Number format
Is ther an equivalent in Access for a for a number format available in Excel .NumberFormat = "[\$-2000000]#0,000.00" So that I can have Hindi numerals in a reports. when I use Numeral shapes to context or system or National it is not giving me the desired result. I want to display the numbers in Hindi format regardless of the system language settings. How I can apply this in a Report so that the field will be formated using the above way? Thanks ...

default number of copies to print
I received a spreadsheet created in Excel 2000 that has a default number of copies to print set at 14. I cannot figure out where in Excel to change this number back to one Thanks in advance Rick print>properties>advanced>paperoutput>copycount -- Don Guillett SalesAid Software donaldb@281.com "R Leeser" <anonymous@discussions.microsoft.com> wrote in message news:93E57562-8153-4B93-88F5-D7D9EB1CDF54@microsoft.com... > I received a spreadsheet created in Excel 2000 that has a default number of copies to print set at 14. I cannot figure out where in Excel to c...

phone numbers #2
how do i add a new phone number field to the drop down list in outlook? You mean besides the 17 or so that are there? You can't add a new type of phone number. Sorry. -- Patricia Cardoza Outlook MVP Author - Special Edition Using Microsoft Office Outlook 2003 Lead Author - Access 2003 VBA Programmer's Reference Author - Absolute Beginner's Guide to Microsoft OneNote 2003 http://blogs.officezealot.com/cardoza "shauna" <anonymous@discussions.microsoft.com> wrote in message news:a23801c486cc\$833670d0\$a601280a@phx.gbl... > how do i add a new phone number field ...

How do I add up texts as fixed number values in a row?
I'm creating a row with drop-down lists with text.I want to create a simple drop-down list with yes and no. Yes would have a background (unseen) value of 1and no would have a value of 0. After items were selected from the drop-down list, I want the unseen value totaled to the far right of the page. I found the following formula which works for columns, but when I try to change the cell range to a row of cells, I get "#N/A". =SUMPRODUCT((C3:C23={"Yes","No"})*({1,0})) Thanks for any assistance. Since "No" has a value of 0 includ...

subtotals #6
Just upgraded to 2003 from 2000 and having a little trouble with the subtotal placement in 2003. I do my sort and then ask for a subtotal based on sales rep name and then a subtotal by customer. The trouble I'm having is with the second subtotal. When subtotaling by customer it skips the line with the subtotal of sales reps name and puts the customer subtotal below. This wasn't a problem with 2000 what changed? Example: customer A xxx customer A xxx subtotal customer ...

Subtotal #5
Every month, I need to take a large spreadsheet, generate subtotals and then produce a report based on the subtotls. Using the Data, Subtotals and collapsing the data allows me to see on screen exactly what I would like to put into my seperate report in email/Word format. How can copy this and paste it without all of the data as well? Thanks in advance. Jimbo Hi Jimbo One way Ctrl+G (or Edit>Goto), select Special, select Visible cells only, Ctrl+C (or Copy) then Paste. -- Regards Roger Govier "Jimbo" <jfroche@sympatico.caSPAMLESS> wrote in message news:et...

Making a number 8 digits long
Hi, can anyone help me?? I am trying to make a number in excel 8 digits long. I have a list of numbers that I have to import to another aplication and have to ensure all of them are 8 digits long. A large number of them are, but there are some that are 6 or 7 long. I am having to manually go through the list and add 0 (zeros) to the front of the number i.e. 01234567 or 00123456. Is ther a formular I can use to do this? Thanks Assuming your numbers are in column A, use this in a helper column: =3DTEXT(A1,"00000000") copy down as required, fix the values, then copy them to overwr...

format a cell so a number in cell will not be included when using the "sum" feature in the status bar.
Using XL2002, I have a spreadsheet where i enter numbers frequently in vairous cells. I would like to be able to select an area, then get the "sum" from the right side of the status bar. There are some cells in the area that i do not want included in the sum. It would be too time consuming to select a group of indivdual cells using the Ctrl key, and since I enter the numbers frequently it is inconvenient to precede the numbers i dont want included in the sum with an apostrophe. Using the Text format does not help. Is there a custom number format i can use in the cells i do not want i...

random number generator #3
I am trying to select a random sample of 76 numbers from excel with the statisticql analysis tools. Do you know what type of distribution should I use? Unfortunately it depends on what you need it for. "Jose Luis" wrote: > I am trying to select a random sample of 76 numbers from excel with the > statisticql analysis tools. Do you know what type of distribution should I > use? ...

Subtotals
Hello, I use subtotals to help me sum the totals of the differing nominal accounts in my management accounts. I collapse the spreadsheet so I can see the totals of each nominal and then highlight each line so that I can format the subtotals making them bold and increase the font size to make them stand out once printed. There can be many nominals and lines that differs from time to time throughout the accounting period. Is there a way I can do this quickley? Regards, Chris. If you mean that you want to bold the amount/count etc for each line that says Total in the leftmost colum...

Why are all page numbers appearing as 3 in TOC?
I created a Table of Contents that displays the pages correctly and e-mailed the doc. When you open that attachment all of the pages in the TOC are displayed as 3. How do I get it to stop doing this? That is a result of opening in Reading Layout view (Full Screen Reading view in Word 2007). There are three possible workarounds, none of which you can control on the sending end but can only recommend to the recipient: 1. Don't open the attachment directly; instead, save it to the hard drive and open it from there (Word uses RL/FSR view only for attachments opened directly)...

Selecting variable records from a form
I've created a checklist form and would like to create a report that demonstrates only the items in the form that have data. SH -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access/200708/1 ...

numbering #4
Is there any system of 'outline numbering' in Excel? Hope i'm not asking for too much! -- Dr. Sachin Wagh MBBS, DHA, DPH Nothing as easy as the outlining within MSWord. Typing the numbers is one way (not very appealing). Using a formula based on the values in the other cells may work--if you can pickout something that would indicate the level--not very nice, either. Dr. Sachin Wagh wrote: > > Is there any system of 'outline numbering' in Excel? Hope i'm not asking for > too much! > -- > Dr. Sachin Wagh > MBBS, DHA, DPH -- Dave Peterson ...

Tax Calculations for subtotal
It will be important to have a configuration to decide the way to calculate Taxes, todAY GP calculates taxes for each line of product in SOP and POP. It will be great to have the choice to calculate taxes directly from the subtotal, because the tax calculations based on each item line is diferent than a percentje over the subtotal directly. It will help a lot to our customers ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If ...