Lost in the weeks

[gin2334]

Hi Guys

im in the process of creating a simple MI package for work that allows me to keep track of the departments performance on an individual level.

We have a simple submission form that send data to a "raw data" page containing the following details.

Business week - Todays Date - Agent name - sales - conversion


Ive then created a table with data validation list at the top that allows the manager to pick his name, "which populates all his team in the table below" then a date "to show that days performance" all populated from the raw data page..............my issue is.

How on earth can I ask the table to populate a sum total for the business week? as each manager submits data daily, each agent will have multiple entries for the same business week, at the moment i've been using "vlookup(data$data" which works fine but obviously wont for multiple entries.

Hope that makes sense? any ideas guys? Access is out of the question as the business doesnt use it.

cheers guys

Paul

 

[Andrew Poulsom]

Check out SUMIF in Excel Help. Example:

=SUMIF('raw data'!A:A,A1,'raw data'!D:D)

 

[gin2334]

Hiya fella,

cheers for the info but i must be having a blonde day, I cant get my head around it....which isnt like me ????

looking at my raw data page now, I have the following:

Business week - Todays Date - Agent name - sales - conversion
39 23/03/09 lee stark 15 40%
39 22/03/09 lee stark 18 45%
37 09/03/09 lee stark 08 16%
37 09/03/09 lee stark 25 61%

my main page has a data validation box with team leader name of which will populate in a table below the name of each of their agents - all sub headings at the side are the same last 3 above....agent name, sales and conversion.
I Need this table to give me a total for eg.Lee starks sales in week 39 and an average conversion for him in that week.

At present the daily table will do a vlookup for the date&name and then give me that days data but I also need to have a seperate table for the weeks totals.

im not sure how the sumif(.....will do this,

looked ast the help page but still lost, can you help anymore.....

 

[Andrew Poulsom]

For more than one condition you need SUMPRODUCT. Example:

=SUMPRODUCT(--('raw data'!A1:A100=A1),--('raw data'!C1:C100=A2),'raw data'!D1:D100)

where A1 contains the week number and A2 the agent name. Adjust the references to suit but make sure they have the same number of rows and avoid using entire columns.

 

[gin2334]

the =sumproduct works a treat however....lat question :O)

how would I get it to work an average % for the week and not the total? it works perfect for the total sales etc but a % needs to be averaged for a week?

the sumproduct is new to me but hey, thanks - im sure i'll use that loads...many thanks


Paul

 

[Andrew Poulsom]

Is it?

=SUMPRODUCT(--('raw data'!A1:A100=A1),--('raw data'!C1:C100=A2),'raw data'!D1:D100,'raw data'!E1:E100)/SUMPRODUCT(--('raw data'!A1:A100=A1),--('raw data'!C1:C100=A2),'raw data'!D1:D100)

 

[gin2334]

hi guys, sorry I didnt respond, my PC packed in and only just got a new one.

I tried the formula but couldnt get it to work so in the end I simply added a column onto the end of the table to work out % of any information that the =sumproduct brought fwd, so simply =sum(a1/a2)*100. works a treat.

New dilema that I hope one of you guys can now help with, The table is now setup so that If I enter a value into cell a1, "such as a business week number" the =sumproduct(.......formulas in the table bring fwd all entries from a raw data page fwd to show me how many sales, calls, products sold etc for each individual.

My next challenge is to somehow ask the table to give me the value for multiple weeks for that I can work out how close to bonus taget someone is for that month. however its not just a simple matter of entering the date.

A bonus month could be week 45 to 49 so I could easily as the table to give me details of performance for week 45, 46, 47, 48 or 49. but can I get it to give me the total for all 5 weeks?

The plan is to add a column at the end of the table for me to enter the target and then ask the table to work out the deficit for each indivdual.

Any ideas cos im well stumped now......MS Access would be such a treat!