How to compute a percentage in pivot table in Excel based on the combination of column & line
I want to compute the percentage of Action that are Done by Category on line axis combined with Level on column axis in a Pivot Table
| Action | Category | Level | Done |
|---|---|---|---|
| 1 | Category 1 | Level 1 | |
| 2 | Category 1 | Level 2 | x |
| 3 | Category 1 | Level 3 | |
| 4 | Category 1 | Level 3 | |
| 5 | Category 2 | Level 3 | |
| 6 | Category 2 | Level 3 | x |
| 7 | Category 3 | Level 1 | |
| 8 | Category 3 | Level 1 | |
| 9 | Category 3 | Level 1 | |
| 10 | Category 3 | Level 1 | |
| 11 | Category 3 | Level 1 | x |
| 12 | Category 3 | Level 2 | |
| 13 | Category 3 | Level 3 | |
| 14 | Category 3 | Level 3 |
Resulting in the following Pivot Table :
| Line | Level 1 | Level 2 | Level 3 |
|---|---|---|---|
| Category 1 | 100% | ||
| Category 2 | 50% | ||
| Category 3 | 20% |
In the Pivot Table of Excel, the only computation option available for the Done field is to compute the Percentage of the Line or the Percentage of the Column. There is no combination of both Line & Column.
Assuming your table is placed in the range A1:D15 (and there are no other data below it), place in cell E2 this formula:
=(IF(COUNTIFS(B$1:B2,B2,C$1:C2,C2,D$1:D2,"x")=1,COUNTIFS(B:B,B2,C:C,C2,D:D,"x")/COUNTIFS(B:B,B2,C:C,C2),0))
Drag the formula down accordingly. Choose the header you like (say "percentage") and write it in cell E1. Create a pivot table similar to the one you have putting level in the columns, category in the rows and percentage in the values. Set values as "sum" and its format as percentage (you can do so by selecting the values area, right-clicking and choosing cells format).
- How to make Colors Mixer in vba
- How to get HEX color value from cell color?
- SSIS: Insert multiple Excel files with same format but different file name to multiple database table destinations based on the Excel filename
- How to randomize Excel rows
- VLOOKUP Returns 0 even though there is a value in the line below the first result
- transpose a table with multiple values in excel
- Trying to late bind outlook to but getting type mismatch error after create object line
- Power Query Transform a Column based on Another Column
- Apply FILTER formula to array (and use filter criteria on specific column in the array)
- Bitly API call using VBA Excel Macro
- excel vba projects not closing
- In a standard MS Access SQL query output that does not have any aliases, how do I replace the full names by their "first-letters" aliases?
- Sigma or Summation function in Excel
- Problem getting the value from a cell using M in excel
- INDEX MATCH to also pull through notes
- Move formatted data in selection from Excel into Word tables
- Excel VBA function to remove letters at end of string
- Can't get all excel processes to stop when closing through Powershell
- Excel function to construct an array from values without VBA
- Alternative method to delete file with out having to add MS Scripting Runtime Reference
- Add new button to worksheet with Excel Add-in
- Apply outline border only to range of cells with openpyxl
- How to Separate Data with Inconsistent Patterns into a Structured Format in Excel
- Excel - Aggregate dynamically values in a Table using array formulas (MAP, BYROW, CHOOSECOLS, TEXTJOIN, LAMBDA, FILTER, HSTACK) in a single Cell
- How to reference the current User\Desktop location in VBA
- Powerpoint chart to excel relative link
- EXCEL: How do I return multiple columns using XLOOKUP, when the lookup_value is a range?
- VBA: How to Loop IF Statement through a Cell and Chart Series
- Incorporating Checkbox to lines of code rather than Cell value
- How do I remove a character from a column of data in Excel