Perfom calculations on rows based on column header names

[mperry76]

I'm trying to calculate some ratios based on numbers in pivot columns. My issue is that when the users filter the pivot, the columns in the pivot change based on what is available for that filter, of course. So the pivot might have 10 columns of data when unfiltered, but only 4 columns when filtered, etc. Therefore my manual calculations (which I have in columns to the right of the pivot) need to be refreshed each time the user filters the pivot.

I've tried inserting calculated fields in the pivot. It works fine for the base records but the issue is that the subtotals in the pivot are not calculating according to the formula. The subtotals in the calculated fields simply add up (sum) the ratios in the field, instead of calculating the ratio, if that makes sense.

Is there a quick fix someone can suggest?

Thanks a lot,
Matt

 

[mperry76]

In fact, I figured it out. I used a simple SUMIF formula on the column headers and the rows, so that even if the column headers change columns because the filter is on, the calculations still work.