Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUM rows
Dear experts,
I've a table which column B to column M are months of 2008, e.g. B1 = Jan 08, C1 = Feb 08 and so forth. Then, in row 10, 15, 22, 35, 42........up to 372 are rows for totals of different departments (the number of rows for each department are not even). My question is, if I want to sum up all departments' totals of any month (say only April), are there any best and quickest ways (formula) to do it. If yes, please kindly advise. Thanks in advance. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUM rows
in row 10, 15, 22, 35, 42........up to 372 are rows for totals
In other words, those rows contain "subtotals" of the rows above them? If that's the case, are there and row headers that identify these rows as "subtotals" ? -- Biff Microsoft Excel MVP "Freshman" wrote in message ... Dear experts, I've a table which column B to column M are months of 2008, e.g. B1 = Jan 08, C1 = Feb 08 and so forth. Then, in row 10, 15, 22, 35, 42........up to 372 are rows for totals of different departments (the number of rows for each department are not even). My question is, if I want to sum up all departments' totals of any month (say only April), are there any best and quickest ways (formula) to do it. If yes, please kindly advise. Thanks in advance. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUM rows
Hi Biff,
Sorry for late reply as I was out of town for a few days. My table is something like that: Jan 08 Feb 08 Mar08..... (Dept A) xxxx 1,000 250 300 xxxx 700 650 450 Total 1,700 900 750 (Dept B) xxxx 150 3000 600 xxxx 1,700 520 800 xxxx 850 150 920 Total 2,700 3,670 2,320 .. .. .. Then for example, I want to sum up all "Total" in Feb08. Are there any fastest way to get a grand totals in Feb08? Thanks & regards. "T. Valko" wrote: in row 10, 15, 22, 35, 42........up to 372 are rows for totals In other words, those rows contain "subtotals" of the rows above them? If that's the case, are there and row headers that identify these rows as "subtotals" ? -- Biff Microsoft Excel MVP "Freshman" wrote in message ... Dear experts, I've a table which column B to column M are months of 2008, e.g. B1 = Jan 08, C1 = Feb 08 and so forth. Then, in row 10, 15, 22, 35, 42........up to 372 are rows for totals of different departments (the number of rows for each department are not even). My question is, if I want to sum up all departments' totals of any month (say only April), are there any best and quickest ways (formula) to do it. If yes, please kindly advise. Thanks in advance. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUM rows
Hi,
The following 2 ways could work: 1. SUM(range)/2 2. Highlight the column of Feb 08 (uptil the cell where you want the grand total) and then click on the Auto sum button on the toolbar. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Freshman" wrote in message ... Hi Biff, Sorry for late reply as I was out of town for a few days. My table is something like that: Jan 08 Feb 08 Mar08..... (Dept A) xxxx 1,000 250 300 xxxx 700 650 450 Total 1,700 900 750 (Dept B) xxxx 150 3000 600 xxxx 1,700 520 800 xxxx 850 150 920 Total 2,700 3,670 2,320 . . . Then for example, I want to sum up all "Total" in Feb08. Are there any fastest way to get a grand totals in Feb08? Thanks & regards. "T. Valko" wrote: in row 10, 15, 22, 35, 42........up to 372 are rows for totals In other words, those rows contain "subtotals" of the rows above them? If that's the case, are there and row headers that identify these rows as "subtotals" ? -- Biff Microsoft Excel MVP "Freshman" wrote in message ... Dear experts, I've a table which column B to column M are months of 2008, e.g. B1 = Jan 08, C1 = Feb 08 and so forth. Then, in row 10, 15, 22, 35, 42........up to 372 are rows for totals of different departments (the number of rows for each department are not even). My question is, if I want to sum up all departments' totals of any month (say only April), are there any best and quickest ways (formula) to do it. If yes, please kindly advise. Thanks in advance. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUM rows
Assuming you column headers are in a calendar sequence as is demonstrated in
your posted sample: =SUMPRODUCT(--(A2:A11="Total"),INDEX(B2:D11,,n)) Where n = month number. For February n = 2 -- Biff Microsoft Excel MVP "Freshman" wrote in message ... Hi Biff, Sorry for late reply as I was out of town for a few days. My table is something like that: Jan 08 Feb 08 Mar08..... (Dept A) xxxx 1,000 250 300 xxxx 700 650 450 Total 1,700 900 750 (Dept B) xxxx 150 3000 600 xxxx 1,700 520 800 xxxx 850 150 920 Total 2,700 3,670 2,320 . . . Then for example, I want to sum up all "Total" in Feb08. Are there any fastest way to get a grand totals in Feb08? Thanks & regards. "T. Valko" wrote: in row 10, 15, 22, 35, 42........up to 372 are rows for totals In other words, those rows contain "subtotals" of the rows above them? If that's the case, are there and row headers that identify these rows as "subtotals" ? -- Biff Microsoft Excel MVP "Freshman" wrote in message ... Dear experts, I've a table which column B to column M are months of 2008, e.g. B1 = Jan 08, C1 = Feb 08 and so forth. Then, in row 10, 15, 22, 35, 42........up to 372 are rows for totals of different departments (the number of rows for each department are not even). My question is, if I want to sum up all departments' totals of any month (say only April), are there any best and quickest ways (formula) to do it. If yes, please kindly advise. Thanks in advance. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUM rows
Hi Biff,
Thanks for your tips and it is working now. Best regards. "T. Valko" wrote: Assuming you column headers are in a calendar sequence as is demonstrated in your posted sample: =SUMPRODUCT(--(A2:A11="Total"),INDEX(B2:D11,,n)) Where n = month number. For February n = 2 -- Biff Microsoft Excel MVP "Freshman" wrote in message ... Hi Biff, Sorry for late reply as I was out of town for a few days. My table is something like that: Jan 08 Feb 08 Mar08..... (Dept A) xxxx 1,000 250 300 xxxx 700 650 450 Total 1,700 900 750 (Dept B) xxxx 150 3000 600 xxxx 1,700 520 800 xxxx 850 150 920 Total 2,700 3,670 2,320 . . . Then for example, I want to sum up all "Total" in Feb08. Are there any fastest way to get a grand totals in Feb08? Thanks & regards. "T. Valko" wrote: in row 10, 15, 22, 35, 42........up to 372 are rows for totals In other words, those rows contain "subtotals" of the rows above them? If that's the case, are there and row headers that identify these rows as "subtotals" ? -- Biff Microsoft Excel MVP "Freshman" wrote in message ... Dear experts, I've a table which column B to column M are months of 2008, e.g. B1 = Jan 08, C1 = Feb 08 and so forth. Then, in row 10, 15, 22, 35, 42........up to 372 are rows for totals of different departments (the number of rows for each department are not even). My question is, if I want to sum up all departments' totals of any month (say only April), are there any best and quickest ways (formula) to do it. If yes, please kindly advise. Thanks in advance. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUM rows
Hi Ashish,
Thanks for your tips. Best regards. "Ashish Mathur" wrote: Hi, The following 2 ways could work: 1. SUM(range)/2 2. Highlight the column of Feb 08 (uptil the cell where you want the grand total) and then click on the Auto sum button on the toolbar. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Freshman" wrote in message ... Hi Biff, Sorry for late reply as I was out of town for a few days. My table is something like that: Jan 08 Feb 08 Mar08..... (Dept A) xxxx 1,000 250 300 xxxx 700 650 450 Total 1,700 900 750 (Dept B) xxxx 150 3000 600 xxxx 1,700 520 800 xxxx 850 150 920 Total 2,700 3,670 2,320 . . . Then for example, I want to sum up all "Total" in Feb08. Are there any fastest way to get a grand totals in Feb08? Thanks & regards. "T. Valko" wrote: in row 10, 15, 22, 35, 42........up to 372 are rows for totals In other words, those rows contain "subtotals" of the rows above them? If that's the case, are there and row headers that identify these rows as "subtotals" ? -- Biff Microsoft Excel MVP "Freshman" wrote in message ... Dear experts, I've a table which column B to column M are months of 2008, e.g. B1 = Jan 08, C1 = Feb 08 and so forth. Then, in row 10, 15, 22, 35, 42........up to 372 are rows for totals of different departments (the number of rows for each department are not even). My question is, if I want to sum up all departments' totals of any month (say only April), are there any best and quickest ways (formula) to do it. If yes, please kindly advise. Thanks in advance. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUM rows
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Freshman" wrote in message ... Hi Biff, Thanks for your tips and it is working now. Best regards. "T. Valko" wrote: Assuming you column headers are in a calendar sequence as is demonstrated in your posted sample: =SUMPRODUCT(--(A2:A11="Total"),INDEX(B2:D11,,n)) Where n = month number. For February n = 2 -- Biff Microsoft Excel MVP "Freshman" wrote in message ... Hi Biff, Sorry for late reply as I was out of town for a few days. My table is something like that: Jan 08 Feb 08 Mar08..... (Dept A) xxxx 1,000 250 300 xxxx 700 650 450 Total 1,700 900 750 (Dept B) xxxx 150 3000 600 xxxx 1,700 520 800 xxxx 850 150 920 Total 2,700 3,670 2,320 . . . Then for example, I want to sum up all "Total" in Feb08. Are there any fastest way to get a grand totals in Feb08? Thanks & regards. "T. Valko" wrote: in row 10, 15, 22, 35, 42........up to 372 are rows for totals In other words, those rows contain "subtotals" of the rows above them? If that's the case, are there and row headers that identify these rows as "subtotals" ? -- Biff Microsoft Excel MVP "Freshman" wrote in message ... Dear experts, I've a table which column B to column M are months of 2008, e.g. B1 = Jan 08, C1 = Feb 08 and so forth. Then, in row 10, 15, 22, 35, 42........up to 372 are rows for totals of different departments (the number of rows for each department are not even). My question is, if I want to sum up all departments' totals of any month (say only April), are there any best and quickest ways (formula) to do it. If yes, please kindly advise. Thanks in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting characters in multiple rows when rows meet specific criteria | Excel Worksheet Functions | |||
Copy rows of data (eliminating blank rows) from fixed layout | Excel Discussion (Misc queries) | |||
How do I view the maximum rows in Excel 2007 (Million Rows)? | Excel Discussion (Misc queries) | |||
Excel 2003 -Rows hidden. Scrolling unhides rows ! How do I stop th | Excel Discussion (Misc queries) | |||
Pivot Tables: How do I show ALL field rows, including empty rows?? | Excel Worksheet Functions |