Subtotal - Additional Formulas based on Subtotal range(s)

[Matt]

[Excel 2003 user, intermediate to advanced Macro skills]

I am working with a document that contains 11,000 items broken down
into 1,000 subtotals using the Excel Subtotal function.

The first 12 columns contain data and the subtotal at column 12 (L) is
a count of the items in that subtotal. There are 21 additional
columns to the right which will be used to record information, and
each of those columns will need subtotal formulas depending on their
data type.

For example, the first subtotal contains 20 rows and the Excel
generated subtotal formula is SUBTOTAL(3,L2:L21)

Columns (S,T,U,AA,AB,AC,AE,AF,AG,AL,AM) contain numerical data which
needs to be SUMmed with a formula like SUM(S2:S21)

Columns (V,W,X,Y,Z,AD,AH,AI,AJ,AK) contain Y/N answers and need to be
counted with a formula like CONCATENATE(COUNTIF(V2:V21,"Y")," /
",COUNTIF(V2:V21,"N"))

The problem I am having, is recreating all of these formulas for each
iteration of the Subtotal. There are 1,000 subtotals, each with
different quantity of rows, so the Range for each subtotal is
different.

How can I recreate these formulas for each Subtotal keeping the
formulas the same for each respective column, but only using the range
for each particular Subtotal?

 

[Pete_UK]

Is there a particular reason why you use Data | Subtotals? Do you
prefer that way of presenting the data, perhaps printing it out with a
page break after every subtotal?

An alternative would be to compile a list of unique items from the
field which you use to insert the subtotals at present (i.e. for each
change in ...). This can be done quite easily using Data | Filter |
Advanced Filter, and the unique list is best placed in a separate
sheet.

From there it is quite easy to get a count (using COUNTIF) and sum
(SUMIF) of columns where the unique item matches the column. You can
use SUMPRODUCT to obtain counts and/or sums for multiple criteria, and
thus build up averages/percentages etc. Other array functions can be
used to give maxima/minima for each category. And once the formulae
are in place they can be copied down to cover your 1000 unique items

No more sorting of data to get it into the order required for Data |
Subtotals. No more waiting for Data | Subtotals to calculate (only to
realise you forgot to set one of the subtotals). And, if you get
another set of data in the following month all you need to do is copy
it over the previous month's data (and remove excess rows), and then
you have your summary table already there.

Having done it both ways, I know which way I prefer.

Hope this helps.

Pete

 

[Matt]

Is there a particular reason why you use Data | Subtotals? Do you
prefer that way of presenting the data, perhaps printing it out with a
page break after every subtotal?

An alternative would be to compile a list of unique items from the
field which you use to insert the subtotals at present (i.e. for each
change in ...). This can be done quite easily using Data | Filter |
Advanced Filter, and the unique list is best placed in a separate
sheet.

From there it is quite easy to get a count (using COUNTIF) and sum
(SUMIF) of columns where the unique item matches the column. You can
use SUMPRODUCT to obtain counts and/or sums for multiple criteria, and
thus build up averages/percentages etc. Other array functions can be
used to give maxima/minima for each category. And once the formulae
are in place they can be copied down to cover your 1000 unique items

No more sorting of data to get it into the order required for Data |
Subtotals. No more waiting for Data | Subtotals to calculate (only to
realise you forgot to set one of the subtotals). And, if you get
another set of data in the following month all you need to do is copy
it over the previous month's data (and remove excess rows), and then
you have your summary table already there.

Having done it both ways, I know which way I prefer.

Hope this helps.

Pete

[Excel 2003 user, intermediate to advanced Macro skills]

I am working with a document that contains 11,000 items broken down
into 1,000 subtotals using the Excel Subtotal function.

The first 12 columns contain data and the subtotal at column 12 (L) is
a count of the items in that subtotal.  There are 21 additional
columns to the right which will be used to record information, and
each of those columns will need subtotal formulas depending on their
data type.

For example, the first subtotal contains 20 rows and the Excel
generated subtotal formula is SUBTOTAL(3,L2:L21)

Columns (S,T,U,AA,AB,AC,AE,AF,AG,AL,AM) contain numerical data which
needs to be SUMmed with a formula like SUM(S2:S21)

Columns (V,W,X,Y,Z,AD,AH,AI,AJ,AK) contain Y/N answers and need to be
counted with a formula like CONCATENATE(COUNTIF(V2:V21,"Y")," /
",COUNTIF(V2:V21,"N"))

The problem I am having, is recreating all of these formulas for each
iteration of the Subtotal.  There are 1,000 subtotals, each with
different quantity of rows, so the Range for each subtotal is
different.

How can I recreate these formulas for each Subtotal keeping the
formulas the same for each respective column, but only using the range
for each particular Subtotal?- Hide quoted text -

- Show quoted text -

OK, so let's say I move the list of unique items to another sheet
(giving me one sheet for summary/subtotals and the original sheet for
the data). I can easily write the SUMIF formulas for the columns that
I need to SUM, but what about the columns where I need to COUNT Y or
N ?

 

[Matt]

Is there a particular reason why you use Data | Subtotals? Do you
prefer that way of presenting the data, perhaps printing it out with a
page break after every subtotal?

An alternative would be to compile a list of unique items from the
field which you use to insert the subtotals at present (i.e. for each
change in ...). This can be done quite easily using Data | Filter |
Advanced Filter, and the unique list is best placed in a separate
sheet.

From there it is quite easy to get a count (using COUNTIF) and sum
(SUMIF) of columns where the unique item matches the column. You can
use SUMPRODUCT to obtain counts and/or sums for multiple criteria, and
thus build up averages/percentages etc. Other array functions can be
used to give maxima/minima for each category. And once the formulae
are in place they can be copied down to cover your 1000 unique items

No more sorting of data to get it into the order required for Data |
Subtotals. No more waiting for Data | Subtotals to calculate (only to
realise you forgot to set one of the subtotals). And, if you get
another set of data in the following month all you need to do is copy
it over the previous month's data (and remove excess rows), and then
you have your summary table already there.

Having done it both ways, I know which way I prefer.

Hope this helps.

Pete

[Excel 2003 user, intermediate to advanced Macro skills]

I am working with a document that contains 11,000 items broken down
into 1,000 subtotals using the Excel Subtotal function.

The first 12 columns contain data and the subtotal at column 12 (L) is
a count of the items in that subtotal.  There are 21 additional
columns to the right which will be used to record information, and
each of those columns will need subtotal formulas depending on their
data type.

For example, the first subtotal contains 20 rows and the Excel
generated subtotal formula is SUBTOTAL(3,L2:L21)

Columns (S,T,U,AA,AB,AC,AE,AF,AG,AL,AM) contain numerical data which
needs to be SUMmed with a formula like SUM(S2:S21)

Columns (V,W,X,Y,Z,AD,AH,AI,AJ,AK) contain Y/N answers and need to be
counted with a formula like CONCATENATE(COUNTIF(V2:V21,"Y")," /
",COUNTIF(V2:V21,"N"))

The problem I am having, is recreating all of these formulas for each
iteration of the Subtotal.  There are 1,000 subtotals, each with
different quantity of rows, so the Range for each subtotal is
different.

How can I recreate these formulas for each Subtotal keeping the
formulas the same for each respective column, but only using the range
for each particular Subtotal?- Hide quoted text -

- Show quoted text -

Pete - to answer your first question, Yes the end-user prefers the
Subtotal way of organizing and viewing data, so I would like to make
the formulas in this method, but I could change if it comes to it.


OK, so let's say I use Advanced Filter to move the list of unique
items to another sheet (giving me one sheet for summary/subtotals and
the original sheet for the data). I can easily write the SUMIF
formulas for the columns that I need to SUM, but what about the
columns where I need to COUNT Y or N ?

 

[Pete_UK]

You can use SUMIF and COUNTIF if there is only one criteria, like:

=SUMIF(Sheet1!A:A,A1,Sheet1!S;S)

assuming your data is on Sheet1. Note that you can use full-column
refrences with these two functions.

Where you have 2 or more criteria you have to use SUMPRODUCT (or array-
entered SUM(IF( ... ). Assuming that the Y/N criteria are in column V,
and the other criterion is in column A, then you would have something
like:

=SUMPRODUCT((Sheet1!A$1:A$12000=A1)*((Sheet1!V$1:V$12000="Y") +
(Sheet1!V$1:V$12000="N")))

The + symbol here acts like OR and the * acts as AND, so this will
count where column A is the same as A1 in Sheet2 (your summary sheet)
and column V contains either Y or N. Note that you can't use full-
column references with SUMPRODUCT (unless you have XL2007), but I've
made the ranges larger than the amount of data you said you had - it's
better not to make these much larger, though, as the unnecessary
calculations will slow the workbook down.

It should be noted that SUMIF and COUNTIF calculate much more quickly
than SP, and so there are ways of speeding things up by using helper
columns on your data sheet. For example, suppose you use column BB in
your data sheet to have a formula like this in BB1:

=A1&V1

and copy this down. Then you can have a COUNTIF formula like this:

=COUNTIF(Sheet1!BB:BB,A1&"Y") + COUNTIF(Sheet!BB:BB,A1&"N")

which will give you the same results as the SP formula.

Hope this helps.

Pete

Is there a particular reason why you use Data | Subtotals? Do you
prefer that way of presenting the data, perhaps printing it out with a
page break after every subtotal?

An alternative would be to compile a list of unique items from the
field which you use to insert the subtotals at present (i.e. for each
change in ...). This can be done quite easily using Data | Filter |
Advanced Filter, and the unique list is best placed in a separate
sheet.

From there it is quite easy to get a count (using COUNTIF) and sum
(SUMIF) of columns where the unique item matches the column. You can
use SUMPRODUCT to obtain counts and/or sums for multiple criteria, and
thus build up averages/percentages etc. Other array functions can be
used to give maxima/minima for each category. And once the formulae
are in place they can be copied down to cover your 1000 unique items

No more sorting of data to get it into the order required for Data |
Subtotals. No more waiting for Data | Subtotals to calculate (only to
realise you forgot to set one of the subtotals). And, if you get
another set of data in the following month all you need to do is copy
it over the previous month's data (and remove excess rows), and then
you have your summary table already there.

Having done it both ways, I know which way I prefer.

Hope this helps.

Pete

[Excel 2003 user, intermediate to advanced Macro skills]
I am working with a document that contains 11,000 items broken down
into 1,000 subtotals using the Excel Subtotal function.
The first 12 columns contain data and the subtotal at column 12 (L) is
a count of the items in that subtotal.  There are 21 additional
columns to the right which will be used to record information, and
each of those columns will need subtotal formulas depending on their
data type.
For example, the first subtotal contains 20 rows and the Excel
generated subtotal formula is SUBTOTAL(3,L2:L21)
Columns (S,T,U,AA,AB,AC,AE,AF,AG,AL,AM) contain numerical data which
needs to be SUMmed with a formula like SUM(S2:S21)
Columns (V,W,X,Y,Z,AD,AH,AI,AJ,AK) contain Y/N answers and need to be
counted with a formula like CONCATENATE(COUNTIF(V2:V21,"Y")," /
",COUNTIF(V2:V21,"N"))
The problem I am having, is recreating all of these formulas for each
iteration of the Subtotal.  There are 1,000 subtotals, each with
different quantity of rows, so the Range for each subtotal is
different.
How can I recreate these formulas for each Subtotal keeping the
formulas the same for each respective column, but only using the range
for each particular Subtotal?- Hide quoted text -

- Show quoted text -

Pete - to answer your first question, Yes the end-user prefers the
Subtotal way of organizing and viewing data, so I would like to make
the formulas in this method, but I could change if it comes to it.

OK, so let's say I use Advanced Filter to move the list of unique
items to another sheet (giving me one sheet for summary/subtotals and
the original sheet for the data).  I can easily write the SUMIF
formulas for the columns that I need to SUM, but what about the
columns where I need to COUNT Y or N ?- Hide quoted text -

- Show quoted text -

 

[Matt]

You can use SUMIF and COUNTIF if there is only one criteria, like:

=SUMIF(Sheet1!A:A,A1,Sheet1!S;S)

assuming your data is on Sheet1. Note that you can use full-column
refrences with these two functions.

Where you have 2 or more criteria you have to use SUMPRODUCT (or array-
entered SUM(IF( ... ). Assuming that the Y/N criteria are in column V,
and the other criterion is in column A, then you would have something
like:

=SUMPRODUCT((Sheet1!A$1:A$12000=A1)*((Sheet1!V$1:V$12000="Y") +
(Sheet1!V$1:V$12000="N")))

The + symbol here acts like OR and the * acts as AND, so this will
count where column A is the same as A1 in Sheet2 (your summary sheet)
and column V contains either Y or N. Note that you can't use full-
column references with SUMPRODUCT (unless you have XL2007), but I've
made the ranges larger than the amount of data you said you had - it's
better not to make these much larger, though, as the unnecessary
calculations will slow the workbook down.

It should be noted that SUMIF and COUNTIF calculate much more quickly
than SP, and so there are ways of speeding things up by using helper
columns on your data sheet. For example, suppose you use column BB in
your data sheet to have a formula like this in BB1:

=A1&V1

and copy this down. Then you can have a COUNTIF formula like this:

=COUNTIF(Sheet1!BB:BB,A1&"Y") + COUNTIF(Sheet!BB:BB,A1&"N")

which will give you the same results as the SP formula.

Hope this helps.

Pete

Is there a particular reason why you use Data | Subtotals? Do you
prefer that way of presenting the data, perhaps printing it out with a
page break after every subtotal?
An alternative would be to compile a list of unique items from the
field which you use to insert the subtotals at present (i.e. for each
change in ...). This can be done quite easily using Data | Filter |
Advanced Filter, and the unique list is best placed in a separate
sheet.
From there it is quite easy to get a count (using COUNTIF) and sum
(SUMIF) of columns where the unique item matches the column. You can
use SUMPRODUCT to obtain counts and/or sums for multiple criteria, and
thus build up averages/percentages etc. Other array functions can be
used to give maxima/minima for each category. And once the formulae
are in place they can be copied down to cover your 1000 unique items
No more sorting of data to get it into the order required for Data |
Subtotals. No more waiting for Data | Subtotals to calculate (only to
realise you forgot to set one of the subtotals). And, if you get
another set of data in the following month all you need to do is copy
it over the previous month's data (and remove excess rows), and then
you have your summary table already there.
Having done it both ways, I know which way I prefer.
Hope this helps.
Pete
[Excel 2003 user, intermediate to advanced Macro skills]
I am working with a document that contains 11,000 items broken down
into 1,000 subtotals using the Excel Subtotal function.
The first 12 columns contain data and the subtotal at column 12 (L)is
a count of the items in that subtotal.  There are 21 additional
columns to the right which will be used to record information, and
each of those columns will need subtotal formulas depending on their
data type.
For example, the first subtotal contains 20 rows and the Excel
generated subtotal formula is SUBTOTAL(3,L2:L21)
Columns (S,T,U,AA,AB,AC,AE,AF,AG,AL,AM) contain numerical data which
needs to be SUMmed with a formula like SUM(S2:S21)
Columns (V,W,X,Y,Z,AD,AH,AI,AJ,AK) contain Y/N answers and need to be
counted with a formula like CONCATENATE(COUNTIF(V2:V21,"Y")," /
",COUNTIF(V2:V21,"N"))
The problem I am having, is recreating all of these formulas for each
iteration of the Subtotal.  There are 1,000 subtotals, each with
different quantity of rows, so the Range for each subtotal is
different.
How can I recreate these formulas for each Subtotal keeping the
formulas the same for each respective column, but only using the range
for each particular Subtotal?- Hide quoted text -
- Show quoted text -

Pete - to answer your first question, Yes the end-user prefers the
Subtotal way of organizing and viewing data, so I would like to make
the formulas in this method, but I could change if it comes to it.

OK, so let's say I use Advanced Filter to move the list of unique
items to another sheet (giving me one sheet for summary/subtotals and
the original sheet for the data).  I can easily write the SUMIF
formulas for the columns that I need to SUM, but what about the
columns where I need to COUNT Y or N ?- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Excellent Pete!

Not exactly what I was looking for, but a much easier solution, and
the end user will just have to adjust to viewing the Summary tab and
the Detail tab seperate. Otherwise making the formulas work within
the Subtotals on the Detail tab would have taken forever (potentially
23,000 formulas).

Thank you very much for your assistance!

 

[Pete_UK]

You're welcome, Matt - thanks for the feedback.

Pete