How to caculate subtotal of every other lines

[Amolin]

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

 

[Frank Kabel]

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

 

[JE McGimpsey]

Take a look at

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

 

[Alan Beban]

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

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

 

[Amolin]

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

 

[AlfD]

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

 

[Amolin]

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

 

[Peo Sjoblom]

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)

 

[AlfD]

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