bearcub
Well-known Member
- Joined
- May 18, 2005
- Messages
- 702
- Office Version
- 365
- 2013
- 2010
- 2007
- Platform
- Windows
I am using a pivot table where the columns and row widths wil change depending upon the sales rep I am reviewing (they are paid in different categories starting with Geo NARR).
I would like to create a 2 way lookup which provide me with the total at the intersection of the Grand Total row and Grand Total Column ($3,873.70)
The next rep I'm validating might only have March-May, or only April payments which impacts the number of columns.
The rows width with vary as well because they have more or less earnings categories (e.g. Geo NARR, Regional NARR, etc).
I've been trying to use the Index match formula but I have not used this approach in a few years so I'm at a loss to set it up. Or, could I use the sumproduct function.
Thank you for your help in advance
I would like to create a 2 way lookup which provide me with the total at the intersection of the Grand Total row and Grand Total Column ($3,873.70)
The next rep I'm validating might only have March-May, or only April payments which impacts the number of columns.
The rows width with vary as well because they have more or less earnings categories (e.g. Geo NARR, Regional NARR, etc).
I've been trying to use the Index match formula but I have not used this approach in a few years so I'm at a loss to set it up. Or, could I use the sumproduct function.
Thank you for your help in advance
| Rep Name | February | March | April | May | Grand Total |
| Joseph Magnum | $230.92 | $1,767.92 | $970.58 | $904.27 | $3,873.70 |
| GEO NARR | $3.09 | $347.58 | $703.08 | $388.65 | $1,442.40 |
| Regional NARR | $6.70 | $1,091.61 | $118.87 | $169.27 | $1,386.45 |
| Multi Year | $1.60 | $1.60 | |||
| Professional Services | $205.00 | $219.78 | $234.44 | $659.22 | |
| Renewal | $16.13 | $100.15 | $148.62 | $111.92 | $376.83 |
| Renewal - Other | $7.20 | $7.20 | |||
| Grand Total | $230.92 | $1,767.92 | $970.58 | $904.27 | $3,873.70 |
