multiple variables through if/and

[Armitage2k]

Hello!

I am developing a worksheet at the moment for an incentive program I am running at my workplace. All relevant data for my statistics will be entered in a list containing:
date - name - receipt no - car - destination - charge - persons - incentive

However, since my Finance department does not need all these infos, I have to make an overview for the whole month, which I did. Now my problem is, I dont want to let my employees enter the information twice, respectively I need a way to get the following information out of this list.

1.
On a specific date, a specific name has how much charge/incentive in total, and how many car requests to the destinations HKX and SZX?
I need to look through the whole list after a specific date, then all these entries from this date must be checked for a specific name, and then I need to count how many car requests have ben given and how much he totally charged and how much he gets as an incentive. Also, I need to know from these specific entries per day per employee, how many were sent to the desitinatinos HKX and SZX (only these 2 available). Basically, it is just a sum and count mixture but I not good enough with excel to do this my own.



Here a more detailed example:
A1 B1 C1 D1 E1 F1 G1 H1
#1 Date Name Receipt No Car Destination Charge Pers. Incentive
#2 24-3 Patrick 123456 Benz HKX 500 1 25
#3 22-3 Jim 654321 Lexus SZX 400 2 20
#4 19-3 Sue 456123 Buick HKX 500 1 25
etc.

I am using office 2003 and really appreciate every help I can get.

THANK YOU VERY MUCH!
A2k

[rylo]

Hi

Can you setup and attach a larger sample in an example workbook. I'm sure you could either use auto filters and the SUBTOTAL function, or else use SUMPRODUCT.

Either way, with a larger sample, give an example or 2 of what you would like to see, and what you would expect the results to be so we know exactly what you are working towards.

rylo

[Armitage2k]

OK, here you go.

the first sheet contains the entries which are made daily. upon this basis I need to fill in several infos on the other sheet, which I will later use as a basis for statistics.
the second sheet contains daily entries for transportation to HKX (Hongkong Airport), SZX (Shenzhen Airport) and CR (Car Rental). I need to fill in the daily total number of sold transportation to those destinations from a specific employee on a specific date.

eg. Patrick (FOPM) has sold on the 1st of January one transportation to HKX for 500,-. So on the second sheet i need to have "1" under HKX and "500" under $$$. The tricky thing though is, that I do not know how many cars they sell per day. I also could have 10 cars sold to HKX and SZX and then I would need this to be devided on the second sheet. Besides, the first sheets is always per month, therefore there are going to be many different dates, thus I first need to sort the date, then the employee, then the destination and from that the total charges.

I hope you understand what I mean

Thanks,
A2k

[Armitage2k]

hmm.... really noone any idea?

Thanks though,
A2k

[rylo]

Hi

Have a look at the attached. I've changed column A from the name and code to just the code (you may like to have that put somewhere else - I was being lazy) and used SUMPRODUCT to bring back the relevant data. See how that goes.

rylo