PDA

View Full Version : SUMIF or Vlookup - How to use in with Range Offset



rajkumar
03-04-2009, 01:54 AM
Hi Experts,
I have an excel report to calculate productivity of engineers.

There is a pivot table (summary call per CE) which has multiple page fields.

When i choose Month as Jan i could see the number of call done by each engineer. Similarly i am able to choose each month and check out calls.

Now what i need is how to use sum if formula with range offset.

here when we choose Jan month there will be 6 columns in the pivot. but when feb is chosen then we have only 5 columns (ie: install column will be absent since there is no new machine installed that month)

I used vlookup to get values from the pivot but i will get only the first match of vlookup not the sum of every match.

I have attached a sample workbook herewith.
Kindly help
Raj :help

Bob Phillips
03-04-2009, 02:14 AM
Couldn't you use GETPIVOTDATA, e.g.


=GETPIVOTDATA("SR NO",A16,"Patch","CHETAN","Merged","1801SFIDA")

rajkumar
03-04-2009, 02:47 AM
Hi Xld,

I tried your suggestion, but it returns only grand total of that engineer.
Let me explain in detail. Engineer 1874 attended calls in engineer 1888 territory as well as engineer 1790 territory also.

Now i want to sum 1874SFIDA attended in other territory also.

Getpivotdata returns me 18 calls for 1874SFIDA

I need - BreakDown 8, PM SM 6, ROT 8 and grand total 22.

In this 8 Break Down calls 7 is of 1874 territory and 1 call in 1888 territory.

Hope i made it clear.

Thanks
Raj : pray2:

Bob Phillips
03-04-2009, 03:08 AM
You can add another name pair to GETPIVOTDATA


=GETPIVOTDATA("SR NO",A16,"Patch","CHETAN","Merged","1801SFIDA","TASK TYPE","Break Down")

rajkumar
03-04-2009, 04:13 AM
i used

=GETPIVOTDATA("SR NO",A16,"Patch","CHETAN","Merged","1801SFIDA","TASK TYPE","Break Down")
But this gives only one engineer's calls within his territory.

1874 has done calls on 1888 and 1790 territory also.

so 1874SFIDA should be 8 and not 7 if i use the formula given.

i want to sum each result of 1874SFIDA in the entire pivot table against each call type (Break down,PM SM, ROT etc)

Pls Help :think:
Raj

Bob Phillips
03-04-2009, 05:09 AM
Isn't that what the total at the end of the row is?