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 rows


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

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/

Take a look at

http://www.mcgimpsey.com/excel/formu...meverynth.html

In article ,
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/

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

Thank you you all...


I mean :When we filter the worksheet by different criteria, the
caculated result will change accordingly...that can be achieved by
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=623366
---
Message posted from http://www.ExcelForum.com/

Hi!

One way:
Put =SUMIF($A$2:$A$25,"=Available",B2:B25) in a cell at the end of the
data in column B. This will add all the values in the range B2:B25
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 to
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 do
variance=requirement - available (or the other way round).

Another way:

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

Alf


---
Message posted from http://www.ExcelForum.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/

Try this

=SUMPRODUCT(--(MOD(ROW($B$2:$B$25),2)=0),$B$2:$B$25,SUBTOTAL(3,O FFSET($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 " wrote in message
...
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/

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.)

Alf


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