Excel Pivot depending upon Header values

Question

I am trying to create a Pivot table in excel and below is my scenario. I have an excel sheet with 5 columns where columns 1,2,3 is having 3 different persons names( person1, person2,person3) as header and it's corresponding values will be either 'yes' or 'No'. column 4 is having amounts and columns 5 will have the share for each person. The share is calculated depending on the value(yes/No) that was present in the 1st three columns and the formula I am using for this is

      (D1/COUNTIFS(A1:C1,"Yes")) 
      i.e., dividing the (amount/ count( of persons having Yes value))

 Person1    Person2 Person3 Amount  PerHeadShare
 Yes        Yes     No       200         100
 Yes        Yes     Yes      300         100
 No         Yes     No       200         200
 No         No      Yes      100         100

Now My requirement is to create a pivot table with two columns

column1 should have 3 rows with Person1,Person2 and Person3

column2 should have sum of the share of amount each person is having

Name          Sum
Person1       200
Person2       400
Person3       200

Slai Slai · Accepted Answer · 2016-08-01T12:39:40

If you have any experience with SQL, you can try something like this:

Data tab > From Access > change file filter to All Files (*.*) and select your Excel file > Open > OK > select PivotTable Report, New Worksheet, and then Properties... > Definition Tab > Command Type: SQL and enter this in Command Text:

SELECT Name, Sum( PerHeadShare ) As [Sum] FROM (
    SELECT 'Person1' As Name, Person1 AS y, * FROM [Sheet1$] UNION ALL 
    SELECT 'Person2' As Name, Person2 AS y, * FROM [Sheet1$] UNION ALL 
    SELECT 'Person3' As Name, Person3 AS y, * FROM [Sheet1$] ) 
WHERE y = 'Yes'
GROUP BY Name

OK > OK > Drag the Name field to Row Labels, drag the Sum field to Values, and then rename the headers in the PivotTable.

In the Command Text "Sheet1" has to be changed to the name of the sheet where the data is with a $ after it, or named range without $ like [named Range], or specific range in the sheet like [Sheet1$B2:F6]

If you have Excel 2010 or above, this can be a bit easier with Power Query or PowerPivot (I have Excel 2007 so I can't try it).

Excel Pivot depending upon Header values

2 Answers