Sum of cells depending on vlookup result

[Epsilon101]

Hi,
This is in Excel..
I am having a bit of trouble figuring a way to do the below, wondering if its easier to do in vba.

Table1

User Name Team Name
User1 Team1
User2 Team1
User3 Team2

This table is set out in this way, giving the user's name and the team they are in, there is 2 teams.

Table2
This table is for MI purposes and isnt normalized.

User User1 User2 User3
IN 3 2 2
OUT 2 1 2
Total 5 3 4

The formula i am trying to do is to total the amount for each team, for each line of data.

So an example is the IN line all users in team1 are user1 and user2, summing the IN amounts totals for the team at 5.

Its easy to do in a range when they are in order, but what happens if they arent.

Vlookup can find the users team name but then how do you use that to pick out specific data in a row below the correct users.

Any help would be great

---------------------------------------

Neil

 

[SkipVought]

Hi,

Given a Username, you could lookup the name across columns using the MATCH function and then, using that result, get the data using the INDEX function.

Skip,

_{[red]Be advised:[/red] It's been reported that a wee psychic is roaming the countryside.
Small Medium @ Large!}

 

[mintjulep]

Sounds like a good application for a pivot table.

 

[SkipVought]

I put your first table in A1:C4
Second table in F1:I4

Here's the formula in C2 and copy down...
[tt]
=INDEX($G$2:$I$4,3,MATCH(A2,$G$1:$I$1,0))
[/tt]


Skip,

_{[red]Be advised:[/red] It's been reported that a wee psychic is roaming the countryside.
Small Medium @ Large!}

 

[Epsilon101]

I used your suggestion skip with the example i gave above,

=INDEX($G$2:$I$4,[COLOR=red]1[/color red],MATCH(A2,$G$1:$I$1,0))

Changed the 3 to a 1 in red above to give me total IN cos ill need one for each line.

So now this formula will pull out the amount to match the user name in A2, how do you set it out to sum users specific to their team, i have got an idea of how it would be done, not sure how to set it out though.





---------------------------------------

Neil

 

[SkipVought]

The answer would be ALOT easier if your data were normailzed. Then a PivotTable solution would be SECONDS away!

Check out: NORMALIZE Your Table using the PivotTable Wizard faq68-5287

Skip,

_{[red]Be advised:[/red] It's been reported that a wee psychic is roaming the countryside.
Small Medium @ Large!}

 

[Epsilon101]

I have it normalized, its all manual data and the users can change during the month, i didnt see much benefit in this layout, but now you mention using a pivot table, it is a good idea. Here is what the example data is

Date Stat User Team Number
01/10/2004 IN User1 Team1 3
01/10/2004 OUT User1 Team1 2
etc..

I have a Total Stat which is a summary of the 2, how would that be done on a pivot table?

Also my users can change monthly, need to set it up so it can be modified easily, all the numbers are still manually inputted unfortunately, which is why i didnt take this layout up to begin with.

---------------------------------------

Neil

 

[Epsilon101]

This example data isnt the same as my full data, i have 3 stats to add in, and only 2 of the stats need to be summed, so it isnt just a case of looking at the total of all stats, needs to be a specific 2.

---------------------------------------

Neil

 

[SkipVought]

[tt]
ROW: User
DATA: Sum of Number
[/tt]
is one way (e pluribus unum)

Skip,

_{[red]Be advised:[/red] It's been reported that a wee psychic is roaming the countryside.
Small Medium @ Large!}

 

[Epsilon101]

here is a bigger example

Date Stat User Team Number Type of day
01/10/2004 AD User1 Team1 2 Early
01/10/2004 IN User1 Team1 3 Early
01/10/2004 OUT User1 Team1 2 Early
01/10/2004 AD User2 Team1 2 Late
etc for each user..
02/10/2004 AD User1 Team1 0 Hols
02/10/2004 IN User1 Team1 0 Hols
02/10/2004 OUT User1 Team1 0 Hols

I have a summary stat field, it is a sum of AD and OUT not including IN so i cant just use the total in the pivot table.

type of day : page
Date : row
Stat : row
User : column
Number : data


Is what i have so far

---------------------------------------

Neil

 

[SkipVought]

real quik
[tt]
Sum of Number Stat
Date User Team Type AD OUT Grand Total
1/10/2004 User1 Team1 Early 2 2 4
User2 Team1 Late 2 2
2/10/2004 User1 Team1 Hols 0 0 0

[/tt]


Skip,

_{[red]Be advised:[/red] It's been reported that a wee psychic is roaming the countryside.
Small Medium @ Large!}