Pivot Table, range including subtotals

[jimt]

I would like to define a range to create a chart. The data spreadsheet is
divided into several sections of row with financial account groups (similar
to departments). Each of the groups has a total line and a blank separator
line between the groups. Each row has a "type" of account. So the data sheet
look something like:

acct type amount
1 A 5.00
2 A 2.00
3 A 1.00
sub-total 8.00

4 B 1.00
5 B 2.00
sub-total 3.00

Group Tot 11.00

6. C 6.00
7. D 3.00
Group Tot 9.00

Grand total 20.00

Is there a way to define a non-contiguious range name or a pivot table that
excludes the "totals" and blank rows so that the resulting chart displays the
sum field for the different type?

Thanks
Jim T.

 

[Shane Devenshire]

Hi,

Assume your original data is in the range A1:B20. Then the easiest way is
to create a separate range, say D1:E20:

Type Total
A
B
C
D
....
And in the Total column enter the formula such as
=SUMIF(A$1:A$20,D1,B$1:B$20)
and copy it down

 

[Shane Devenshire]

Hi,

I just noticed that you have two type combined into one total, to deal with
that try

=SUMPRODUCT(SUMIF($A$1:$A$10,{"C","D"},$B$1:$B$10))

 

[Luke M]

You can setup your PIvotTable/Chart, with account being in the row section,
and then de-select "Sub Total, Grand Total, and Blank"

Other option is when you go to make your normal chart and are selecting the
cells to use, hold down Ctrl to select multiple ranges.

 

[Don]

Your example looks almost like the result of a Pivot Table , but if you start
with that and as I put it into a sheet I have cell A1 through C17 looking
like your sample. I then created a pivot table with that range and selected
"Acct" and excluded (blank) and subtotals to get a line for each account 1-7
with a total of 20.

I would prefer to have this all as a pivot , but hey, it should work this
way also.