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.

Attached Files

Test1.xlsx (9.9 KB, 20 views)

Perhaps

__________________
Using O365 v2503 - 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

SUMIF() should do the trick quite nicely.


=SUMIF(B2:B20,"Medical",C2:C20)

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.

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.

Attached Files

Test1.xlsx (14.1 KB, 11 views)

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 .