# Excel: Is there a formula to reference a row of pivot table

I've been working with Excel for many years and I have never come up with a solution to this problem. I have a pivot table which is performing all of my basic summaries. The data is divided into "Items" along the row fields and "Weeks" along the columns with a sum of my "Quantity" in the Values field. What I want is to be able to reference a row of this data and use it in a sumproduct formula. Think of it as using GETPIVOTDATA to return a row instead of one value. It would use it this way:

`=SUMPRODUCT('PivotRow','PivotRow' > 10)`

What this formula would do is exclude all of the PivotFields where the value was below 10. What I don't know how to do is reference a row of a PivotTable. Is there some super special way to use GETPIVOTDATA that I am not aware of? I love PivotTables to build summaries of data but I typically need to build an analysis on top of that summary which can be very frustrating. Any help is greatly appreciated.

I am working with PowerPivot so I have that functionality available to me as well.

## Related posts

- Refreshing powerpivot data automatically
- Excel – How to replace the formula in a cell with the output of the formula
- Python – Extracting raw data from a PowerPivot model using Python
- How to display nulls in PowerPivot using DAX
- Excel – How to get the proper average in a pivot based on pivot data
- C# – Prevent Excel from deleting reference in formula when row is deleted
- Excel – How to copy a formula down a column into every 3rd row
- Excel – Using formula to reference values on other sheets
- Powerpivot using If function
- Excel 2007 get value of cell for formula not reference
- Excel – in Excel , in the pivot tables when i drop down the row fields, value filter option is disabled
- Excel formula to findout if date is between two other date and get the value of next column of that row
- Excel – Why the formula is showing error – Inconsistent calculated column formula in MS Excel
- Excel – SUM until last row using VBA to add formula in Excel
- Excel – Find duplicate text in a row with formula in excel
- Excel – Using the value in a cell as a cell reference in a formula
- Excel – Using VBA to insert formula into cell with variable row numbers
- Excel formula to lookup formula 1 cell below & 3 cells to the right of reference
- Excel – How to preserve a formula’s reference to a worksheet when the worksheet is deleted and replaced
- Excel – give an ID to each row. How to do this with a formula
- Excel – Reference next or previous sheet in a formula if the sheet number/name is a varible/variant
- Excel formula to find reference used by other cell
- Excel – Insert New Row After Last Row in Worksheet and Copy Format and Formula from Cell Above
- Excel – Why is the Excel Power Pivot not maintaining the order of the manually re-arranged items in the ROW field
- Excel – this row number placeholder excel formula
- Excel – worksheet.Cells(row,col).Formula
- Excel – Formula to reference data from a PivotTable
- Excel – Pivot Calculated formula: SUM(Field1)/AVG(Field2)
- C# – Excel.Worksheet.Cells[row,col] = “=Formula” vs. Range.set_Value(Missing.Value, arrayFormulas)
- Excel – Formula to find matching row value based on cells in multiple columns
- PowerPivot DAX – Dynamic Ranking Per Group (Min Per Group)
- Excel formula -> how to change SUMPRODUCT formula to skip null cells
- Excel – Is this the RegEx for matching any cell reference in an Excel formula
- C# – How to check a Cell contains formula or not in Excel through oledb reader or excel library, excel datareader or NPOI etc (Except Interop)
- Excel – How to reference Value instead of formula in a formula using Excel
- Excel – Formula to pull cell from rightmost column with data (knowing row number)
- Excel – Automatically increment sheet reference when dragging formula
- Powerpivot adding calculated item to a field
- Datatype conversion failed for measure in PowerPivot workbook
- Excel – VBA formula application-defined or object-defined error when inserting formula in VBA
- C# – Adding formula to Excel with C# – making the formula shown
- Excel – Return a cell reference for use in a formula
- C# – Excel – Copy and paste hyperlink formula without formula
- How to force powerpivot relationship to be one-to-one
- Excel – How to reference a tab name with a formula
- PowerPivot DAX: Identify Max & Min Value per Group
- Excel Formula – Check if cell has formula
- Excel – Pass parameter to PowerPivot query
- Excel – Representing an Excel cell address in a formula using the string values of other Excel cells
- Excel – Connecting PowerPivot to PostgreSQL database
- Excel – Copying formula to the next row when inserting a new row
- Measure as date variable when calculating time periods in Powerpivot
- Excel – Formula in Excel that references another Excel file based on cell reference
- Excel – Using “INDIRECT” function in the “SUMPRODUCT” formula for True/False Cell Reference
- Excel – How to turn a string formula into a “real” formula
- Excel – How to express cell id using string letter and current row number in an Excel formula
- Compare values in the same column in adjacent rows in PowerPivot
- Sql-server – excel powerpivot update error “object was not found in the cube”
- Calculate the number of days between today and specific date using DAX in Powerpivot
- Excel – How to give reference to a row number in excel formula
- Excel – insert row keeping formula in excel
- Excel – Using the PowerPivot automatic refresh code
- Excel – the meaning of “&” when dealing with condition formula with cell reference rather than fixed numbers
- Excel – How to overcome the max length limit of a formula in Excel? a bug of excel
- Excel – Return Cell Reference from Lookup Formula without using Match
- Excel – How to increment row value in excel INDEX formula using fill handle
- Calculating a Ratio using Column A & Column B – in Powerpivot/MDX/DAX, not in SQL
- Excel – How to add a Calculated Item(Not Field) with PowerPivot whilst pointing to the DataModel
- Excel – Changing connection data in PowerPivot file
- Excel – How to make multiple pivot tables mimic the filters (report and row label) of another on just that sheet in excel
- Excel formula to reference ‘CELL TO THE LEFT’
- Excel – How to reference value in cell to the left within a formula
- Sql-server – Combining multiple datasets in PowerPivot
- Excel – A formula to copy the values from a formula to another column
- Excel formula to reference cells in another (closed) workbook with dynamic file path
- Excel – Using a column reference for a vlookup formula
- Difference between PowerPivot, PowerQuery and PowerBI
- R – Exporting data from PowerPivot
- Excel PowerPivot Average Calculation
- Excel – How to get the formula to always reference to the last sheet
- Excel – Reference a cell as a string within a formula
- How to SUM a Column based on another column in DAX Powerpivot
- Excel – How to select a whole column or row in a formula in excel?
- Excel: pass array argument to formula through cell reference
- Excel – PowerPivot: How to identify Max Value per Group in a Calculated Column
- C# – Connecting to PowerPivot with C#
- Excel – Converting Recorded VLookUp formula in R1C1 into VBA formula using A1 reference
- Excel – VBA code in Excel to add a row to multiple sheets and then copy formula from adjacent row
- Excel – On workbook open, Excel Macro to refresh all data connections sheets and pivot tables and then export the pivot to csv
- Excel – How to shift the reference cell in a formula
- Excel VBA: Formula with reference to cell in another worksheet (variable)
- EXCEL formula calculating the sum of a column for the last 7 days from today as a reference
- Excel – Cell Reference for Formula in Loop in Excel VBA
- Excel – How to dynamically choose an excel sheet to reference in a formula
- Excel – VBA Formula If: WorksheetFunction, Formula, or R1C1

## Best Solution

A potential approach to this problem is to use cube sets or named sets to extract out that specific value form the cube source instead of actually querying the actual PivotTable instead.

For more information, check out: http://dennyglee.com/2010/02/04/pivots-cube-formulas-named-sets-oh-my/.