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.

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.

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.

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.

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.

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.

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.

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.