Quote:
Originally Posted by scho63
I need Pivot Table / Data sort help and appreciate anyone who can assist. I've watched about 25 Youtube videos and cannot find the correct process on how to do this.
I'm a B- with Pivot Tables. I have one created and got a bunch of data I needed but one thing I am stuck with is how to group a range of numbers and then the sum of how MANY of those numbers
Exact need:
- I have list of 12,000 records with a list of states in one column and the number of Units they have in the other
- I want to group by States and THEN have a grouping / filter by the segments below to have a total - Power Units 50-100
- Power Units 101-250
- Power Units 250-500
- Power Units 500-1000
- Power Units > 1001
The end result would be like this
Alaska
Power Units 50-100 12
Power Units 101-250 6
Power Units 250-500 4
Power Units 500-1000 1
Power Units 1001 0
Alabama
Power Units 50-100 44
Power Units 101-250 12
Power Units 250-500 3
Power Units 500-1000 5
Power Units 1001 2
Can anyone solve my dilemma? 
|
Per Chat GPT:
To achieve the desired result with a Pivot Table in Excel, you can follow these steps:
Open your Excel file with the data.
Select the range of data, including the column headers. In your case, select both the "State" column and the "Units" column.
Go to the "Insert" tab in the Excel ribbon and click on "PivotTable" (in the "Tables" group). The "Create PivotTable" dialog box will appear.
Make sure the "Select a table or range" option is selected and that the correct range is displayed in the "Table/Range" field. Also, choose where you want to place the PivotTable (e.g., in a new worksheet or an existing one).
Click "OK" to create the PivotTable.
You should now see the PivotTable Field List on the right side of the Excel window.
In the PivotTable Field List, drag the "State" field to the "Rows" area.
Drag the "Units" field to the "Values" area.
In the "Values" area, you should see "Sum of Units" (or similar). Click on the drop-down arrow next to it and choose "Value Field Settings."
In the "Value Field Settings" dialog box, select the "Sum" function (which should be the default) and click "OK."
Now, to group the units into the desired segments, right-click on any value in the "Sum of Units" column in the PivotTable and select "Value Field Settings" again.
In the "Value Field Settings" dialog box, click on the "Number Format" button.
In the "Number" tab of the "Format Cells" dialog box, choose "Custom" from the Category list.
In the "Type" field, enter the following custom format: "Power Units 0-100;;Power Units 101-250;;Power Units 251-500;;Power Units 501-1000;;Power Units > 1000"
Click "OK" in the "Format Cells" dialog box, and then click "OK" again in the "Value Field Settings" dialog box.
Now, your PivotTable should display the states grouped by the specified segments, with the corresponding sums next to each segment.
Note: If your Excel version doesn't support the custom format specified in step 14, you may need to modify the format to suit your version.