#1
|
|||
|
|||
Vlookup
In the attached file, I am using VLOOKUP in sheet 1 to extract the number of employees belong to each division. Medical Division was in Department 1 then was transferred to Dept 3 then to Dept 4. I want to add its 3 numbers (500 + 450 + 470) To calculate the average and the maximum number (500). The problem is that VLOOKUP read the 1st record only. I appreciate your help. |
#2
|
||||
|
||||
Perhaps
Code:
=SUMPRODUCT(--(B2:B20="medical")*C2:C20)
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#3
|
||||
|
||||
SUMIF() should do the trick quite nicely.
=SUMIF(B2:B20,"Medical",C2:C20) |
#4
|
|||
|
|||
Vlookup
Thanks a lot Colin Legg and Pecoflyer for your great support. I have tried the 2 functions plus subtotal & Sumifs and try to merge with MAX function to get the highest number of employees in each division but without success.
My problem is that I found that some of these divisions were repeated 2 times and 4-5 times in other times because of the organization historical reconstructions. Any additional support will be appreciated. Thank you. |
#5
|
||||
|
||||
Sorry, I missed that you want the average and max as well.
Sum =SUMIF(B2:B20,"Medical",C2:C20) Average =AVERAGEIF(B2:B20,"Medical",C2:C20) Max (this is an array formula, CTRL+SHIFT+ENTER, not just ENTER) =MAX(IF(B2:B20="Medical",C2:C20)) However, if you want to repeat this exercise for each division then you should use a pivot table. I have attached an example. |
#6
|
|||
|
|||
Vlookup
Colin Legg and Pecoflyer,
I do really appreciate your great support. My actual situation was complicated where I received from personal Dept 20,000 records represent 2000 Depts & divisions with their manpower 7 years history. However, I have just succeeded after 5-7 hours of working and I would never do it without your support. Once more, thank you very much and let me know if you have complicated issues so I can solve it for you . |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Using Vlookup & IF together | thelauncher | Excel | 5 | 08-25-2013 11:32 PM |
Vlookup | ibrahimaa | Excel | 8 | 01-03-2012 09:32 PM |
Can i do this with a VLookup? | foodstudent | Excel | 1 | 01-21-2011 12:34 AM |
Using IF & VLOOKUP together | junction | Excel | 7 | 11-18-2010 05:15 AM |
Help with VLOOKUP | sakhtar | Excel | 2 | 07-24-2010 07:39 PM |