Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Current Setup

  • A table (Table1) that has the following fields:
    • Organization Name
    • Person Name
    • Several fields that users can put an "X" into (each represents a "module" that users would need access to)
    • A sum of the # of modules needed (countif on the range for "X" values)
    • "full licenses": displays 1 if more than 3 modules needed
    • "Limited licenses": display 1 if <= 3 modules needed
  • On a separate sheet, two input fields for cost per each full & limited license

Goal

  • Create a table showing, per organization, what its cost will be for full and limited license that each Org has selected.

So far

I have been able to create the pivot table showing the number of full and express licenses per organization.

Question

How can I add an additional column to the pivot table that will take the sum for a group (e.g. the sum of # of full licenses for an organization) and multiply that by the cost of the given license type, so that I can calculate the amount each group would owe?

share|improve this question
    
I am confused on what you want to multiply by. Is it B16 and B17 or B22 and B23, per license? –  Raystafarian Jul 25 '13 at 16:58
    
The pivot table shows the sum of each type of license per group. I'd essentially like the following additional fields: full license cost (pivot sum * B16), full license maintenance (PT sum * B19), limited license cost (pt sum * B17), and limited license maintenance (pt sum * B20) –  SeanKilleen Jul 25 '13 at 17:15
    
Eventually, I'd like to also to sum up the total purchase cost and total yearly cost from the calculated fields I mention in the comment above as well. –  SeanKilleen Jul 25 '13 at 17:16

1 Answer 1

up vote 1 down vote accepted

I have not looked at your workbook, but it seems to me that you can add a column to your source data that holds cost of the record (i.e., license). Where I2:J2 are the Full License and Limited License fields (0 or 1) and Sheet2!B16:B17 are the costs for full and limited licenses, use the following formula in your new Cost field.

=SUMPRODUCT(I2:J2,TRANSPOSE(Sheet2!$B$16:$B$17))

Enter the formula as an array formula by pressing Ctrl+Shift+Enter. Fill the formula down your table.

Then you can update your pivot source range and add Sum of Cost to your Values.

enter image description here

share|improve this answer
    
While I didn't use this exact technique, I realized that calculating per line on the table (in hidden columns) and then using the pivottable to sum is the easiest way in this case. Thanks for helping me see the simpler answer! –  SeanKilleen Jul 25 '13 at 18:28

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.