Summation of a column based off of a calculated column value…

[bashcraft]

I'm working in a pivot table where the column I would like to SUM is ever changing based on whatever data I filter. I've identified (possibly over complicated) the formula I need to calculate the column, but I can't find a formula that will give me the sum with that information.

Example:

=SUBSTITUTE(ADDRESS(1,MATCH(HLOOKUP("Total sum of Last Mo",7:7,1,FALSE),7:7,0),4),"1","")

This formula currently returns the value D.

I need a formula that will SUM(D:D) based off of the above formula. Again, the SUM formula I'm looking for will change depending on my Pivot Table filters and the result of my substitute equation. (With some filters the formula will need to sum column AP).

I am not opposed to changing the letter to the corresponding numeric value if it means the formula will work...

Please help!!!

 

[Derek Brown]

Hello and welcome to The Board.
You did not say which version of Excel is being used but what you probably need to use is GETPIVOTDATA with, perhaps, SUMIF?
Have a look at the following podcasts for ideas:
http://www.youtube.com/watch?v=hL5K_XJa6VY
http://www.youtube.com/watch?v=h1m4OnaSLzs
http://www.youtube.com/watch?v=f3a1xf7-fPk
If you search YouTube for "MrExcel GETPIVOTDATA" you will find a few more.

 

[bashcraft]

Thanks for the reply Derek, I'll take a look and see what I find.

Thanks,

Brett
Excel 2010