Vlookup.. Or SUMIF's?

**carrieuk** · 11-03-2014, 05:57 AM

Hi,

I have a excel spreadsheet that we use for wages, it has a front tab with the names listed in Column A and the sites in Row 1. Not every person works on every site so someone might work on 1 site out of 8.

My question is I am trying to put on the back of these individual sheets but I want it to look up the persons name and list the sites they have worked at - I can then SUMIFS the site names and person to get the values. I've attached a mock up version of what I'm trying to do - it's very basic but I am trying to learn.

I'm not sure what formula to use.

Many thanks

Carrie

**Olly** · 11-03-2014, 06:05 AM

Edit: Thanks for changing the title

**bmouse** · 11-03-2014, 06:08 AM

You could use a Pivot Table for this. Have a look.

**carrieuk** · 11-03-2014, 06:16 AM

Hmm.. I'm not sure that will work as on the second tab in Column A i need to be able to put in the sites that e.g JR has worked at only and leave any blanks out. I can then SUM the amounts. Is this possible with a formula or will it need a macro? Maybe I'm looking at this wrong?

**boopathiraja** · 11-03-2014, 06:27 AM

HI, Ref the attachment, array formulas used, you have to give ctrl+shift+enter not just enter

**Pete_UK** · 11-03-2014, 06:29 AM

As your second sheet has no data in it, it is difficult to see where the data would come from. Please attach another sample file with made-up data on the second (and a third) sheet, and show how the first sheet is to be derived from it.

Pete

**bmouse** · 11-03-2014, 06:30 AM

Perhaps you can use the data provided by the Pivot Table? I only suggest Pivot Table because in a list like this it can filter data and sum values quickly without formulas. It can also be updated with a simple click, if the data in your original list changes.