Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table, range including subtotals
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table, range including subtotals
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 -- If this helps, please click the Yes button. Cheers, Shane Devenshire "jimt" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table, range including subtotals
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) ) -- If this helps, please click the Yes button. Cheers, Shane Devenshire "jimt" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table, range including subtotals
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. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "jimt" wrote: 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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table, range including subtotals
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. "jimt" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot table not including new data | Excel Discussion (Misc queries) | |||
Q: Including subtotals in a pivot chart | Excel Discussion (Misc queries) | |||
Q: Including subtotals in a pivot chart | Charts and Charting in Excel | |||
Pivot Table subtotals | Excel Discussion (Misc queries) | |||
Pivot table subtotals 100% | Excel Discussion (Misc queries) |