Calculated Field Totals for IF Formulas

[t_beaupre]

I'm attempting to get a calculated field to properly total results from an IF formula referencing two data sets, however it will only display the total of whichever data set meets the criteria rather than calculating a dynamic total based on the criteria. Probably sounds weird, so I'll try to give a concrete example.

Say you're attempting to calculate travel expenses that your company will refund you, but there's a maximum to how much you can expense against certain categories. You want to be able to input all of your expenses into a table that will continue to update on a pivot until it hits the maximum allowed amount per category, in which case it defaults to the maximum allowed amount for that category.

My dataset is something like this:
Columns: Expense Category, Expense Amount, Max Allowed Amount

Expense Category may have several charges against a single category (e.g. 4 different gas receipts for road travel). I used a pivot to auto-sum all expenses within a given category, to then be compared to the Max Allowed Amount.
I'm now trying to create a calculated field that will tell me how much I can expense after a trip, called Actual Expenses. The formula I'm using is;

=IF(Expense Amount <= Max Allowed Amount, Expense Amount, Max Allowed Amount)

While the formula will properly group all of the data into rows organized by Expense Category, the Actual Expenses subtotal will display a subtotal for Expense Amount, or Max Allowed Amount, rather than totaling the resulting amounts in the above rows. Results may show as below;

Row Labels Expense Amount Max Allowed Amount Actual Expenses
Cat 1 13 4 4
Cat 2 16 10 10
Cat 3 13 14 13
Cat 4 8 20 8
Totals 50 48 48

Actual Expenses should total to 35, however it's pulling the total for Max Allowed Amount because that total meets the formula conditions over the Expense Amount Total. I'd like to be able to total the values that display in the pivot rows.

Any ideas?

[protonLeah]

To attach a Workbook
(please do not post pictures or links to worksheets)

• Click Advanced (next to quick post),
• Scroll down until you see "Manage Attachments",
• Click that then select "add files" (top right corner).
• Click "Select Files" find your file, click "open" click "upload"
• Once the upload is completed the file name will appear below the input boxes in this window.
• Click "Done" at bottom right to close the Attachment Manager.
• Click "Submit Reply"

[romperstomper]

You can't achieve that with a calculated field in a pivot table. If you have PowerPivot you can probably create a measure for it, otherwise I'd suggest using formulas instead of a pivot table.