How to caculate subtotal of every other lines

for example

T,    t1, T2, T3, T4
A,    3,   4 ,  2,   4
b,    3,   1,   3,   5
C,    3,   1,   3,   3
d,    3,   4,   5,   1
E,    3,   3,   3,   2
f,     3,   1,   3,   1 

I want to get the subtotal of A,C,E rows and b,d,f row

--
Message posted from http://www.ExcelForum.com

0
7/12/2004 5:03:41 AM
excel.misc 78881 articles. 5 followers. Follow

8 Replies
528 Views

Similar Articles

[PageSpeed] 25

Hi
for column A try
=SUMPRODUCT(--(MOD(ROW(A1:A100),2)=1),A1:A100)
(for your 'a, c, e' rows)

and
=SUMPRODUCT(--(MOD(ROW(A1:A100),2)=0),A1:A100)
for the 'b, d, f' rows

-- 
Regards
Frank Kabel
Frankfurt, Germany


> for example
> 
> T,    t1, T2, T3, T4
> A,    3,   4 ,  2,   4
> b,    3,   1,   3,   5
> C,    3,   1,   3,   3
> d,    3,   4,   5,   1
> E,    3,   3,   3,   2
> f,     3,   1,   3,   1
> 
> I want to get the subtotal of A,C,E rows and b,d,f rows
> 
> 
> ---
> Message posted from http://www.ExcelForum.com/
0
frank.kabel (11126)
7/12/2004 6:20:56 AM
Take a look at

    http://www.mcgimpsey.com/excel/formulae/sumeverynth.html

In article <Amolin.199ls3@excelforum-nospam.com>,
 Amolin <<Amolin.199ls3@excelforum-nospam.com>> wrote:

> for example
> 
> T,    t1, T2, T3, T4
> A,    3,   4 ,  2,   4
> b,    3,   1,   3,   5
> C,    3,   1,   3,   3
> d,    3,   4,   5,   1
> E,    3,   3,   3,   2
> f,     3,   1,   3,   1 
> 
> I want to get the subtotal of A,C,E rows and b,d,f rows
> 
> 
> ---
> Message posted from http://www.ExcelForum.com/
0
jemcgimpsey (6723)
7/12/2004 7:24:31 AM
Amolin < wrote:

> for example
> 
> T,    t1, T2, T3, T4
> A,    3,   4 ,  2,   4
> b,    3,   1,   3,   5
> C,    3,   1,   3,   3
> d,    3,   4,   5,   1
> E,    3,   3,   3,   2
> f,     3,   1,   3,   1 
> 
> I want to get the subtotal of A,C,E rows and b,d,f rows
> 
> 
> ---
> Message posted from http://www.ExcelForum.com/
> 
Not sure what you mean by "subtotal", but if the functions in the freely 
downloadable file at http://home.pacbell.net/beban are available to your 
workbook, the following will return the total of rows A, C and E:

=SUM(ArrayAlternates(A2:E7))

and the following will return the total of rows b, d, and f:

=SUM(ArrayAlternates(A2:E7,FALSE))

Alan Beban
0
unavailable (273)
7/12/2004 4:31:13 PM
Thank you you all...


I mean :When we filter the worksheet by different criteria, th
caculated result will change accordingly...that can be achieved b
subtotal function.

I attached a example workbook , when I hide some rows , 
the result can refect the total of "availabe", "requirement
,"Variance" of each(Hided rows not included)

                 Attachment filename: book1.xls                
Download attachment: http://www.excelforum.com/attachment.php?postid=62336
--
Message posted from http://www.ExcelForum.com

0
7/21/2004 8:33:09 AM
Hi!

One way:
Put  =SUMIF($A$2:$A$25,"=Available",B2:B25) in a cell at the end of th
data in column B. This will add all the values in the range B2:B2
which are in the same row as "Available" in column A.

Copy this across the columns.
Similar formula for "Requirement" (be careful to match the ranges t
the formula's location: it's not automatically a "copy" job).
Variance you could also do in the same way but it is simpler to d
variance=requirement - available (or the other way round).

Another way:

When you use Autofilter to view only selected rows, the Excel automati
sum in the status bar will show the sum of the visible cells. Get it t
give you answers by selecting a whole column at a time.

Al

--
Message posted from http://www.ExcelForum.com

0
7/21/2004 12:27:20 PM
Thank you you all;

I tend to use this formula in worksheets with filer on, 
I hope this formula only caculate visible cells, 
how can I do? 

Could any one give me a example

--
Message posted from http://www.ExcelForum.com

0
7/23/2004 12:49:32 AM
Try this

=SUMPRODUCT(--(MOD(ROW($B$2:$B$25),2)=0),$B$2:$B$25,SUBTOTAL(3,OFFSET($B$2,R
OW($B$2:$B$25)-MIN(ROW($B$2:$B$25)),,)))

-- 
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)



"Amolin >" <<Amolin.19tnci@excelforum-nospam.com> wrote in message
news:Amolin.19tnci@excelforum-nospam.com...
> Thank you you all;
>
> I tend to use this formula in worksheets with filer on,
> I hope this formula only caculate visible cells,
> how can I do?
>
> Could any one give me a example?
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
terre081 (3244)
7/23/2004 1:03:23 AM
Peo:

According to his spreadsheet, OP's data looks like this:

Available ................	2.............	2
Requirement...........1.21.........	1.01
Variance................. 	0.79........	0.99
Available...............  	18...........	18
Requirement...........	20.48......	14.43
Variance................. -2.48........	3.57

where 22  columns are months and there are 8 groups of 3 rows.

So it's every third row.
The original post bears no relationship to this.

(I broke my own rules and downloaded it. No harm done. Yet.)

Al

--
Message posted from http://www.ExcelForum.com

0
7/23/2004 8:47:42 AM
Reply:

Similar Artilces:

Media player just plays black screen and coloured lines
Anything I try to play is just black outline/blobs overlaid with bright coloured lies. I have tried re-downloading the player and re-installing but same prob exists? I don't know why! Please help if you can :) What's the file type of the videos you're trying to play (AVI, WMV...)? Try updating the video card drivers to their latest version. Regards -- Tim De Baets http://www.bm-productions.tk Sandy wrote: > Anything I try to play is just black outline/blobs overlaid with bright > coloured lies. I have tried re-downloading the player and re-installing but ...

Cache Rebuild Every Time
Every single time I open my copy of Visio (2002 Pro on XP Pro machine) it rebuilds the cache, which takes forever. Why is it doing this every time and how can I stop it/ Thanks Keith, Is this previous thread any help? http://groups-beta.google.com/group/microsoft.public.visio/browse_frm/thread/e68944ff51e65200/a5e39bc162753fa7?tvc=1 Richard Price "Keith" <@.> wrote in message news:emQhzvs4EHA.1404@TK2MSFTNGP11.phx.gbl... > Every single time I open my copy of Visio (2002 Pro on XP Pro machine) it > rebuilds the cache, which takes forever. > > Why is it doing...

Moving data labels around with VBA (and adding a line between two
Hello. I am looking for a way to manage data labels in an automated charting procedure I am writing. The chart is a waterfall chart (I believe) and I'd like to be able to loop through each dataseries/column to find the height of the total column and then set the label for the total column to sit x points above it. There is a second issue I need help with. As the chart displays components of return for stocks and bonds, I'd like to place a line in between the last stock and the first bond shown. Is there a way that I can place this line dynamically (charts will like have diff...

message routing based on subject line
Our firewall inserts [SPAM] at the beginning of each spam message subject line. I would like to route those tagged messages to a public folder. I have not found a way to control message routing based on subject line. Any assistance would be appreciated. Thank you. "Milton" <milton.bliss@sunwestmanagement.com> wrote in message news:1167842798.615252.282430@a3g2000cwd.googlegroups.com... > Our firewall inserts [SPAM] at the beginning of each spam message > subject line. I would like to route those tagged messages to a public > folder. I have not found a way to control...

drag point on line chart to change data in associated cell
This same file is working in excel 2000, now using 2007. I have a line chart that is made up of cell data. I want to select a data point on the line chart and drag it up or down and have it change the data in the associated cell... this is used as input data for other functions... Unfortunately, that was removed in Office 2007 A number of users have asked to have it back but it is not present in the Tech Preview version of Excel 2010 best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "cmax" <cmax@discussions.microsof...

New on-line services at institution but Money 2004 doesn't show th
Fairly recently my credit card issuer added online account download services. I have gone to the institution and used it to download a qif file. But when I click on this institutions name in the list presented by on-line services manager it doesn't show any services. (The name is in the list.) Is there a process where the list is updated with newly offered services? If so, how long should it take for a new one to appear. Or is there someone I can notify with the name of the institution who can then add the service. Thanks -- Harris S. Freeman Alliance Strategies and Programs ...

Line Weight does not Print correctly
I have a document that I have several data graphics used. I use a callout circle for my shape and then I like data using the data graphics. I select a line weight of 9 and all looks great on the screen. But then when I go to print it, the lines weights on a couple of the data graphics are correct, but the rest print in what appears to be a line weight 1. Does any one know why this happens? ...

Pivot tables % of subtotal
Is there a way to display data like I've shown it below. In case it is unclear, I'd like to calculate a percentage of sales of size 1 to the total sales of the various sizes of a product. For example, Prod A, size 1, as a percentage of total sales of Prod A, sizes 1-3. Product Size Units % A 1 100 16.7% A 2 200 33.3% A 3 300 50.0% A Total 600 100.0% B 1 150 20.0% B 2 350 46.7% B 3 250 33.3% B Total 750 100.0% Total 1350 100.0% It looks like you want to review Debra Dalgleish's notes at: http://www.contextures.com/xlPivot10.html Excel GuRu wrote: > ...

is their a filter for Sage Line 50 v7 for Msoft Small Business Fi.
...

Re: how to add one same word on every 10th line
hi, i have excel with 50000 lines.i have to insert this word "###break###" on every 10 th line.how to do this? i tried macro but cannot able to do.i need help.advance thanks for you help -- elaveni ----------------------------------------------------------------------- elavenil's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3752 View this thread: http://www.excelforum.com/showthread.php?threadid=57167 For i = Cells(Rows.Count,"A").End(xlup).Row To 10 Step -1 If i Mod 10 = 0 Then Rows(i).Insert cells(i,...

Adding deposit to Subtotal???
Recently posted a transaction for a customer in GP 8.0. The customer's balance on their Card ADDED the deposit to the subtotal on the invoice. So instead of showing the correct A/R of $200, it shows they owe $400. The invoice was for $300 with a $100 deposit. (Don't worry, we didnt' enter a negative deposit. If that's possible anyways). The actual invoice and distribution accts are correct as is the G/L. The problem only shows in the Customer's card and the A/R Trial Balance. Any help is appreciated. Thanks so much! ...

Line chart functionality
I'm trying to create a chart in excel and can't get it to do what I want. I'm tracking data points over time and I want the area below the line to be shaded when a certain value is True. So my line chart will have sections where it is shaded under the line (up to the line) and periods where it is not shaded. Can anyone help me accomplish this...Thanks! Stan sueland@samaritanasset.com [This followup was posted to microsoft.public.excel.charting with an email copy to Stan Ueland. Please use the newsgroup for further discussion.] You can pull this off with a couple of building b...

How do I add grid lines to a LONG list with 3 columns?
I am trying to switch from my old PC that used works to this new Hewlett-Packard that uses Excel. When I downloaded my file of this use list of library books (Column 1 = Author's last name, Column 2 = authors 1st name, Column 3 = Book title), it didn't include the grid lines to separate the columns and the rows (of which there are over 1,000). How do I get it to do so?....and, how do I get it to keep each row numbered? I've really tried to find the answer to such a simple question, but it's not happening. Your help would be appreciated. You can number each row by addin...

Excel pie chart title words break to next line between letters?
I am working on a Powerpoint 2003 slide with an Excel pie chart embeded in it. One of the titles (the word "convection") breaks between the "o" and "n" in "tion". I can't seem to be able to adjust the space allotted. How can I correct this? -- Sweetsweed ...

Automating copy and paste of subtotals to new sheet
Hi everyone, Have about 240,000 lines of CSV. Column A can have duplicates and is the key. Can import about 65,000 rows at a time into excel, to deal with the maximum # of rows, but if I try to subtotal then it breaks the limit. What I want to accomplish is: All rows are sorted by Column A first. For each set of rows with the same values in column A, sum columns D, E, F, G, H, I, J. Then take the value in column A and the subtotals above, and populate a single row on a new sheet with these values . Then repeat the exercise, starting with the first row that contains a different value in C...

can't use Subtotal when table is madewit menu ADD, Table.
Hi, i use excel 2007 (i translate the menu names because i use another language). I made a table, typing the row of the fieldnames and all the record. Then, using menu "ADD" and under Tables, i clicked on Table. So my table was created at once with the name 'Table1'. Remark: i noticed that the range referenced by Table1 does not include the row of the fields. Now i want to use subtotals (like in version 2003). But the problem is that, when i click into the table, 'Subtotal' in menu Data is not available. I made a second table manually without using menu "...

GP printing PM checks to Acuprint using ACL command line
Looking for experience of printing Great Plains PM checks to Acuprint. Have client that is trying to do this by adding ACL command line in check format report writer to tell Acuprint which template to use. Anyone successful in doing this? Thanks. ...

Columns showing up as thin vertical lines
We have a chart that is showing the thin vertical lines for the columns. We tried resetting the default settings and it changed several things, but not the columns . . . they remained thin vertical lines. We have tried changing the gaps and have tried deleting/recreating the chart. There are several other charts which were created in this file that are just fine. Oh, and our chart does not preview any different. We have found that if we remove enough data points the lines ever so slightly widen to thin columns. This chart is not showing any greater amount of data than the others i...

RMS 1.3 Off Line
When the database goes off-line, what is the procedure for entering opening/closing amounts after the database comes back on-line? Currently, the opening amounts are not included in the closing Z report. There isn't a blind closed batch visible in the journal viewer. Can Anyone help with this? There is NO information in help, PDF Manual or anywhere Else I can Find. Since this happens 100% of the time, Since the first day of installation there MUST be a reason RMS is doing this?????? "cigarguy" wrote: > When the database goes off-line, what is the procedure for enterin...

How to turn every produt in RMS OFF of sale
What is the query i would run in the administrator to turn all items OFF sale (unclick the ON SALE box) Thanks!! Hi Jessica It appears the field that tracks the "item is on sale" flag is saletype, where if its zero it means the item is not "on sale", if its 1 then the item is on sale, and if 2 its on sale based on a schedule. I ran this query to confirm this select itemlookupcode, description, saletype from item Items not on sale all had their saletype set to zero. Once you're comfortable with your analysis, backup the database and run update item set saletyp...

Multiple lines of criteria without repeating header
Hi I'm using the database functions DMAX etc and want to have a variety o criteria in a column. However I believe the "criteria" range has to b a minimum of 2 rows with the top row containing headers. If I want t reference a row of the range of criteria, I am then lacking th headers. Including the headers would then include all rows of criteri in between too - creating an undesirable OR condition. Eg: ----B--------C--------D 2--Angle--Angle---Max 3-->=1----<3-----=DMAX(dbase,2,B2:C3) 4-->=3----<5-----=DMAX(dbase,2,B2:C4) 5-->=5----<6-----=DMAX(dbase,2,B2:C5) 6...

Data showing incorrectly on line chart #2
I've asked this question before, but since FOR THE LIFE OF ME I can't figure out how to bring up my previous question on the Microsoft board, I've posted it again. (Before I get tons of answers on how to pull it up again, let me just state that I've probably tried them all before. I've run my screen name through Google and found the question on other boards, but since I'm not a member of those boards, unless I join, I can't post a question/answer there. I've run the EXACT question through this MS board and haven't come up with a thing. I've ru...

combining a stacked column and line chart together
I need to know how I combine a stacked column chart with a line chart (2 or more lines are required on the chart) with two different y axis values. Create a stacked column chart using all of the data. Select a column series in the stack that you want to change to a line. Go to Chart menu > Chart Type, and select a line type. Repeat as needed for other series. Double click on any series you want to move to the secondary axis, and on the Axis tab, select Secondary. Repeat as needed. You should try to keep all of the stacked columns on the same axis, otherwise they will not stack corr...

How do I arch a line of text within a banner?
I am trying to design a banner that will have two lines of text. Rather than both lines being straight, I would like to arch the top line over the second line so that I can insert artwork between the two lines of text. Can this be accomplished with this program? If not, is there a program available the I could use instead. Thank You. *Brandon* numberonesignguy wrote: > I am trying to design a banner that will have two lines of text. > Rather than both lines being straight, I would like to arch the top > line over the second line so that I can insert artwork between the > two...

Macro insert lines
This is my macro to insert lines if the value in b chanbes. I want it to inset 26 lines it only inserts 1 please help Sub Deilv() Dim LastRow As Long Dim row_index As Long Application.ScreenUpdating = False LastRow = ActiveSheet.Cells(Rows.Count, "b").End(xlUp).Row For row_index = LastRow - 1 To 26 Step -1 If Cells(row_index, "B").Value <> _ Cells(row_index + 1, "B").Value Then Cells(row_index + 1, "B").EntireRow.insert _ (x24ShiftDown) End If Next End Sub Hi Esrei, Try: Sub Deilv() Dim LastRow As Long Dim...