What is the best formula to use? Vlookup with Sumif embedded?

[lalaexcellala]

Hi
I want to reproduce information from one report into another spread sheet and the information needs to be reconfigured in the second spread sheet. I need to do this on a monthly basis.

The first report has been pivoted and has three columns of data: Location; Role; Count of number of Role

In the second spread sheet, I want to reproduce information about some of the locations, some of the roles and include a count of each role at each location.

I've attached an example spread sheet with tabs representing each of the reports.

I thought I could use a vlookup formula or match or index to find the right location and then go to the next column to look for the right role and then use a sumifs formula to work out the count e.g
(SUMIFS(C3:C64,B3:B64,"OFFICER/SENIOR")

But I can't make it work and am confused by how to embed the formulas.

Can you please advise me if I'm on the right track or whether I should use a different formula for this task?

Thanks in advance for your help

 

[NARAYANK991]

Hi ,

If possible , upload the proper workbook , which has the raw data and the pivot table in it , and manually put in a few entries of what the outputs should be in the output tab.

Narayan

 

[lalaexcellala]

Hi ,

If possible , upload the proper workbook , which has the raw data and the pivot table in it , and manually put in a few entries of what the outputs should be in the output tab.

Narayan

Unfortunately I don't think I can because the information is confidential.
Is there some other way I can clarify what I'm trying to do for you?

 

[NARAYANK991]

Hi ,

You can replace the confidential data with random data ; what is important is the data layout , both input and output.

At the least , can you manually fill in the output tab in the file you have already uploaded ?

Narayan

 

[lalaexcellala]

Hi,

I've added a tab called output to show what I want - does this help?