PivotTable -- how to sum the result of a countif formula by group?

1

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?

SeanKilleen

Posted 2013-07-25T15:58:54.920

Reputation: 1 972

I am confused on what you want to multiply by. Is it B16 and B17 or B22 and B23, per license? – Raystafarian – 2013-07-25T16:58:07.297

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 – 2013-07-25T17:15:34.253

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 – 2013-07-25T17:16:09.067

Answers

1

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

Excellll

Posted 2013-07-25T15:58:54.920

Reputation: 11 857

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 – 2013-07-25T18:28:20.687