A little help with sumif criteria trying to use INDEX and MATCH

[Epsilon101]

Hi,

I am having a little trouble trying to figure a way of doing this, i tried to do it the =INDEX( ,MATCH(),MATCH()) way, but its not setup to do it very easily.

I have got sheets 'Summary' and 'Users'

'Users' is a table with 2 columns as below
USER ID TEAM
BOBB Team 1
PHILD Team 1
DAVIEST Team 2
..etc

and 'Summary' is a big table like below

Date Stat BOBB PHILD DAVIEST TDS 01/09/2004 Stat1 1 2 5
Stat2 3 4 2
Stat3 6 5 10
Stat4 2 6 2
02/09/2004 Stat1 3 6 2
Stat2 4 7 1
Stat3 7 2 11
STat4 3 7 3

etc... this is all made up dates go down to end of the month.
TDS Field is Team Daily Summary, there will be 2 of this for team 1 and 2.

For team 1 TDS the idea is to for each stat line sum the numbers under each name that is in team 1.

Any ideas on how to do this?? My example of the data in 'Summary' is very simple compared to the actual layout, but thats what is shows. Short of adding the team name to the left of the number under each name on the stat line like below.

Date Stat Team Name BOBB
01/09/2004 Stat1 Team 1 1
Stat2 Team 1 3
Stat3 Team 1 6
Stat4 Team 1 2
02/09/2004 Stat1 Team 1 3
Stat2 Team 1 4
Stat3 Team 1 7
STat4 Team 1 3



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

Neil

 

[Epsilon101]

Argh spacing is a tad messed up doh sorry didnt check it
but i guess u can get the idea. Just also realised that the second option would work either, because there would be a team name list for each user and the table being indexed would have loads of columns of each team name and i can only use one when using MATCH.. argh

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

Neil

 

[SkipVought]

Neil,

It's ALWAYS harder to analyse, process, report on data in a NON-NORMALIZED format, which is what you have on the Summary Sheet. Aggregation (summary data), ought to the the result of reporting out of a normalized data repository, maybe something like...
[tt]
Date UID Stat Value
01/09/2004 BOBB 2 3
01/09/2004 BOBB 3 5
01/09/2004 BOBB 4 2
...
[/tt]
A structure like this would probably make waht you want alot easier to accomplish.

Skip,

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

 

[Epsilon101]

Hi Skip,
yeah i do try to keep my tables normalized, just couldnt think of a good way of doing this one. Doing it the way you have explained is a good way, its just that at the moment there are 29 users, 4 stats for each so its 116 rows for each day and for this month thats going to be 3480 rows * 4 columns, and its meant to be used as a summary for management


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

Neil

 

[Epsilon101]

Ill see if there is a good way for me to get it into 3NF so i can break up this data a bit more.

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

Neil

 

[Epsilon101]

Not sure how i could do this in 3NF, it would need to be reliant only on a primary key, only thing i could use as a primary key is the date.

At the moment in 1NF ive got

Date Stat User Team Value

With details as above.

I suppose a composite primary key could be date & user.

Team is dependent on user.
Stat is always the same 4 Stat1,2,3,4 each day for each user so dependent on user and date, except stat 4 is a calculation (Stat1 + Stat3).
The value is dependent on the User, stat & Date.

I am aiming to get this to 2NF first before trying 3NF, any ideas Skip??

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

Neil

 

[SkipVought]

I need to understand the data better. Please explain your data elements. For instance, I don't understand what various stats categories are.

Skip,

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

 

[Epsilon101]

Its a table to show each users type of transactions for each day.

So username is BOBB and team is team 1
Each user has 4 stats to be added each day, stats 1 to 4 are ACD,IN,OUT, and TOTAL OUT.

Total out is a calculation (ACD + OUT).

As above the normalization you gave as an example would be first normal form from what i can see. I usually use normalization when working on dbase tables rather than MI in excel so this time i didnt.

I have had a think about it earlier today, and probably the best way to do it is to have two separate input tables one for team 1 and one for team 2. Then have a main table with them both on, which would have the info linked into it from the two separate tables, and i could then do the sums i wanted to have in each separate table and link them on to the main one.

If you could give any info on how you would normalize this data so its at maybe second or third, because third if i remember is all the info is reliant on the primary key, using a date as a primary key here isnt good practise so im not sure how i would go about it, maybe add my own row numbers.

Only reason i ask, is im thinking of importing this data into access to try and speed up the process.

Thanks for any help Skip


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

Neil

 

[SkipVought]

I believe that I'd go with 2 tables

[tt]
Stats Teams
Date UID
UID Team
ACD
IN
OUT
[/tt]
Total is a aggrergate function, which does not belong in a table unless table access performance is an issue, which I doubt.

Skip,

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