If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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/ |
#2
|
|||
|
|||
How to caculate subtotal of every other lines
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/ |
#3
|
|||
|
|||
How to caculate subtotal of every other lines
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/ |
#4
|
|||
|
|||
How to caculate subtotal of every other lines
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 |
#5
|
|||
|
|||
How to caculate subtotal of every other lines
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/ |
#6
|
|||
|
|||
How to caculate subtotal of every other lines
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/ |
#7
|
|||
|
|||
How to caculate subtotal of every other lines
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/ |
#8
|
|||
|
|||
How to caculate subtotal of every other lines
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/ |
#9
|
|||
|
|||
How to caculate subtotal of every other lines
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/ |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Excel Chart Vertical Lines Corrupting? | Ken Black | Charts and Charting | 4 | June 4th, 2004 09:16 PM |
What are these lines | Hector | New Users | 2 | June 2nd, 2004 01:35 PM |
PROBLEM: Making form/report with a fixed no. of lines and variable no. or records | Chris | Using Forms | 0 | May 31st, 2004 11:46 AM |
Bug in Excel 2003 Subtotal Function | Clint Erickson | Worksheet Functions | 2 | May 28th, 2004 06:52 PM |
Count subtotal lines | Debbie | Worksheet Functions | 4 | October 16th, 2003 12:09 AM |